在 Oracle 中，将 number(5,10) 转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9328878/
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
In Oracle, convert number(5,10) to date
提问by cool ahao
When ececute the following SQL syntax in Oracle, always not success, please help.
在Oracle中执行以下SQL语法时，总是不成功，请帮忙。
40284.3878935185 represents '2010-04-16 09:18:34', with microsecond.
an epoch date of 01 January 1900 (like Excel).
40284.3878935185 代表“2010-04-16 09:18:34”，微秒。
1900 年 1 月 1 日的纪元日期（如 Excel）。
create table temp1 (date1 number2(5,10));
insert into temp1(date1) values('40284.3878935185');
select to_date(date1, 'yyyy-mm-dd hh24:mi:ssxff') from temp1
Error report: SQL Error: ORA-01861: literal does not match format string 01861. 00000 - "literal does not match format string" *Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra whitespace. *Action: Correct the format string to match the literal.
错误报告：SQL 错误：ORA-01861：文字与格式字符串 01861 不匹配。00000 - “文字与格式字符串不匹配” *原因：输入中的文字必须与格式字符串中的文字长度相同（例外领先的空白）。如果已打开“FX”修饰符，则文字必须完全匹配，没有多余的空格。*操作：更正格式字符串以匹配文字。
Thanks to Mark Bannister
感谢马克班尼斯特
Now the SQL syntax is:
现在的 SQL 语法是：
select to_char(to_date('1899-12-30','yyyy-mm-dd') +
date1,'yyyy-mm-dd hh24:mi:ss') from temp1
but can't fetch the date format like 'yyyy-mm-dd hh24:mi:ss.ff'. Continue look for help.
但无法获取像“yyyy-mm-dd hh24:mi:ss.ff”这样的日期格式。继续寻求帮助。
采纳答案by Alex Poole
Simple date addition doesn't work with timestamps, at least if you need to preserve the fractional seconds. When you do to_timestamp('1899-12-30','yyyy-mm-dd')+ date1(in a comment on Mark's answer) the TIMESTAMPis implicitly converted to a DATEbefore the addition, to the overall answer is a DATE, and so doesn't have any fractional seconds; then you use to_char(..., '... .FF')it complains with ORA-01821.
简单的日期添加不适用于时间戳，至少如果您需要保留小数秒。当您这样做时to_timestamp('1899-12-30','yyyy-mm-dd')+ date1（在对 Mark 答案的评论中）在添加之前TIMESTAMP隐式转换为 a DATE，整体答案为 a DATE，因此没有任何小数秒；然后你用to_char(..., '... .FF')它抱怨 ORA-01821。
You need to convert the number of days held by your date1column into an interval. Fortunately Oracle provides a function to do exactly that, NUMTODSINTERVAL:
您需要将date1列保留的天数转换为间隔。幸运的是，Oracle 提供了一个函数来做到这一点NUMTODSINTERVAL：
select to_timestamp('1899-12-30','YYYY-MM-DD')
+ numtodsinterval(date1, 'DAY') from temp3;
16-APR-10 09.18.33.999998400
You can then display that in your desired format, e.g. (using a CTE to provide your date1value):
然后您可以以您想要的格式显示它，例如（使用 CTE 来提供您的date1价值）：
with temp3 as ( select 40284.3878935185 as date1 from dual)
select to_char(to_timestamp('1899-12-30','YYYY-MM-DD')
+ numtodsinterval(date1, 'DAY'), 'YYYY-MM-DD HH24:MI:SSXFF') from temp3;
2010-04-16 09:18:33.999998400
Or to restrict to thousandths of a second:
或者限制为千分之一秒：
with temp3 as ( select 40284.3878935185 as date1 from dual)
select to_char(to_timestamp('1899-12-30','YYYY-MM-DD')+
+ numtodsinterval(date1, 'DAY'), 'YYYY-MM-DD HH24:MI:SS.FF3') from temp3;
2010-04-16 09:18:33.999
An epoch of 1899-12-30sounds odd though, and doesn't correspond to Excel as you stated. It seems more likely that your expected result is wrong and it should be 2010-04-18, so I'd check your assumptions. Andrew also makes some good points, and you should be storing your value in the table in a TIMESTAMPcolumn. If you receive data like this though, you still need something along these lines to convert it for storage at some point.
的时代1899-12-30听起来很奇怪，虽然，你说和不对应到Excel。您的预期结果似乎更有可能是错误的，应该是 2010-04-18，所以我会检查您的假设。Andrew 也提出了一些很好的观点，您应该将您的值存储在表中的一TIMESTAMP列中。但是，如果您收到这样的数据，您仍然需要按照这些方式将其转换为存储。
回答by
Using an epoch date of 30 December 1899, try:
使用 1899 年 12 月 30 日的纪元日期，尝试：
select to_date('1899-12-30','yyyy-mm-dd') + date1
回答by tbone
Don't know the epoch date exactly, but try something like:
不知道确切的纪元日期，但请尝试以下操作：
select to_date('19700101','YYYYMMDD')+ :secs_since_epoch/86400 from dual;
Or, cast to timestamp like:
或者，转换为时间戳，例如：
select cast(to_date('19700101', 'YYYYMMDD') + :secs_since_epoch/86400 as timestamp with local time zone) from dual;
回答by Andrew Wolfe
I hope this doesn't come across too harshly, but you've got to totally rethink your approach here.
我希望这不会太苛刻，但是您必须在这里完全重新考虑您的方法。
You're not keeping data types straight at all. Each line of your example misuses a data type.
您根本没有保持数据类型直。您示例的每一行都误用了一种数据类型。
- TEMP1.DATE1 is not a date or a varchar2, but a NUMBER
- you insert not the number 40284.3878935185, but the STRING >> '40284.3878935185' <<
- your SELECT TO_DATE(...) uses the NUMBER Temp1.Date1 value, but treats it as a VARCHAR2 using the format block
- TEMP1.DATE1 不是日期或 varchar2，而是 NUMBER
- 您插入的不是数字 40284.3878935185，而是 STRING >> '40284.3878935185' <<
- 您的 SELECT TO_DATE(...) 使用 NUMBER Temp1.Date1 值，但使用格式块将其视为 VARCHAR2
I'm about 95% certain that you think Oracle transfers this data using simple block data copies. "Since each Oracle date is stored as a number anyway, why not just insert that number into the table?" Well, because when you're defining a column as a NUMBER you're telling Oracle "this is not a date." Oracle therefore does not manage it as a date.
我大约 95% 肯定您认为 Oracle 使用简单的块数据副本传输这些数据。“既然每个 Oracle 日期都存储为一个数字，为什么不直接将该数字插入表中呢？” 好吧，因为当您将列定义为 NUMBER 时，您是在告诉 Oracle“这不是日期”。因此，Oracle 不会将其作为日期进行管理。
Each of these type conversions is calculated by Oracle based on your current session variables. If you were in France, where the '.' is a thousands separator rather than a radix, the INSERT would completely fail.
这些类型转换中的每一个都由 Oracle 根据您当前的会话变量计算。如果你在法国，那里的'.' 是千位分隔符而不是基数，则 INSERT 将完全失败。
All of these conversions with strings are modified by the locale in which Oracle thinks your running. Check dictionary view V$NLS_PARAMETERS.
所有这些字符串转换都由 Oracle 认为您运行的语言环境修改。检查字典视图 V$NLS_PARAMETERS。
This gets worse with date/time values. Date/time values can go all over the map - mostly because of time zone. What time zone is your database server in? What time zone does it think you're running from? And if that doesn't spin your head quite enough, check out what happens if you change Oracle's default calendar from Gregorian to Thai Buddha.
日期/时间值会使情况变得更糟。日期/时间值可以遍布整个地图 - 主要是因为时区。您的数据库服务器在哪个时区？它认为你在哪个时区奔跑？如果这还不够让您头疼，请查看如果您将 Oracle 的默认日历从公历更改为泰国佛陀会发生什么。
I strongly suggest you get rid of the numbers ENTIRELY.
我强烈建议你完全摆脱这些数字。
To create date or date time values, use strings with completely invariant and unambiguous formats. Then assign, compare and calculate date values exclusively, e.g.:
要创建日期或日期时间值，请使用具有完全不变和明确格式的字符串。然后专门分配、比较和计算日期值，例如：
GOODFMT constant VARCHAR2 = 'YYYY-MM-DD HH24:MI:SS.FFF ZZZ'
GOODFMT 常量 VARCHAR2 = 'YYYY-MM-DD HH24:MI:SS.FFF ZZZ'
Good_Time DATE = TO_DATE ('2012-02-17 08:07:55.000 EST', GOODFMT);
Good_Time DATE = TO_DATE ('2012-02-17 08:07:55.000 EST', GOODFMT);
