在 Oracle DB 中将浮点数转换为日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6242097/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert float to DateTime in Oracle DB
提问by Andriy Petrivskyy
I have the Oracle table with the column that stores date and time in float representation. It looks like this:
我有一个 Oracle 表，其中的列以浮点表示形式存储日期和时间。它看起来像这样：
40610.389837963 -> should be decoded to 5/11/2011 16:06
40676.2641666667 -> should be decoded to 5/13/2011 6:20
I know the encoded value and decoded value, but I don't know how to decode this float format to "normal" date time :-)
我知道编码值和解码值，但我不知道如何将此浮点格式解码为“正常”日期时间:-)
Could someone explain or give me a link when this is described? Any help will be very appreciated
有人可以在描述时解释或给我一个链接吗？任何帮助将不胜感激
Thanks in advance. Andriy.
提前致谢。安德烈。
回答by Codo
The date/time format you have is the one Excel uses on Windows. If assign a number format to a date cell in Excel, you'll see the same numbers. You can also put the number into an Excel cell and assign it a date format to reveal the date.
您拥有的日期/时间格式是 Excel 在 Windows 上使用的一种格式。如果为 Excel 中的日期单元格指定数字格式，您将看到相同的数字。您还可以将数字放入 Excel 单元格并为其指定日期格式以显示日期。
It's basically the number of days since the 1st January, 1900 except that Excel has some mistakes regarding it's leap year handling.
它基本上是自 1900 年 1 月 1 日以来的天数，只是 Excel 在处理闰年方面存在一些错误。
You can turn it into a proper date using the following SQL expression:
您可以使用以下 SQL 表达式将其转换为正确的日期：
select to_date('12/30/1899', 'MM/DD/YYYY') + 40676.2641666667 from dual;
The number 40610.389837963 is converted to 3/8/2011 09:21 however. But I guess you mixed that up somewhere.
然而，数字 40610.389837963 被转换为 3/8/2011 09:21。但我猜你把它搞混了。
