在Oracle中执行以下SQL语法时，总是不成功，请帮忙。
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
错误报告：SQL 错误：ORA-01861：文字与格式不匹配
细绳
01861. 00000 - “文字与格式字符串不匹配”
*原因：输入中的文字必须与中的文字长度相同
格式字符串（前导空格除外）。如果
“FX”修饰符已打开，文字必须完全匹配，
没有多余的空格。
*操作：更正格式字符串以匹配文字。
感谢 Mark Bannister
现在 SQL 语法是：
select to_char(to_date('1899-12-30','yyyy-mm-dd') +
date1,'yyyy-mm-dd hh24:mi:ss') from temp1
但无法获取像 'yyyy-mm-dd hh24:mi:ss.ff' 这样的日期格式。继续寻求帮助。
When ececute the following SQL syntax in Oracle, always not success, please help.
40284.3878935185 represents '2010-04-16 09:18:34', with microsecond.
an epoch date of 01 January 1900 (like 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.
Thanks to Mark Bannister
Now the SQL syntax is:
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.
发布评论
评论（4）
使用纪元日期 1899 年 12 月 30 日，尝试：
Using an epoch date of 30 December 1899, try:
简单的日期添加不适用于时间戳，至少如果您需要保留小数秒的话。当您执行
to_timestamp('1899-12-30','yyyy-mm-dd')+ date1（在对马克答案的评论中）时，TIMESTAMP隐式转换为DATE在添加之前，总体答案是DATE，因此没有任何秒小数部分；然后你使用to_char(..., '... .FF')它会抱怨 ORA-01821。您需要将
date1列保存的天数转换为 间隔。幸运的是，Oracle 提供了一个函数来实现这一点，NUMTODSINTERVAL< /code>：然后您可以以所需的格式显示它，例如（使用 CTE 提供您的
date1值）：或者限制为千分之一秒：
1899-12-30的纪元听起来很奇怪，并且与您所说的 Excel 不符。您的预期结果似乎更有可能是错误的，应该是 2010-04-18，所以我会检查您的假设。 Andrew 也提出了一些很好的观点，您应该将您的值存储在表中的TIMESTAMP列中。如果您收到这样的数据，您仍然需要按照这些方式在某个时候将其转换为存储。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) theTIMESTAMPis implicitly converted to aDATEbefore the addition, to the overall answer is aDATE, and so doesn't have any fractional seconds; then you useto_char(..., '... .FF')it complains with 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:You can then display that in your desired format, e.g. (using a CTE to provide your
date1value):Or to restrict to thousandths of a second:
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 aTIMESTAMPcolumn. If you receive data like this though, you still need something along these lines to convert it for storage at some point.不确切知道纪元日期，但请尝试以下操作：
或者，转换为时间戳，例如：
Don't know the epoch date exactly, but try something like:
Or, cast to timestamp like:
我希望这不会让人觉得太严厉，但你必须完全重新考虑你的方法。
您根本没有保持数据类型的直接性。您的示例的每一行都滥用了一种数据类型。
我大约 95% 确定您认为 Oracle 使用简单块数据副本传输此数据。 “既然每个 Oracle 日期都存储为数字，为什么不直接将该数字插入表中呢？”嗯，因为当您将列定义为 NUMBER 时，您是在告诉 Oracle“这不是日期”。因此，Oracle 不将其作为日期进行管理。
这些类型转换中的每一个都是由 Oracle 根据当前会话变量计算的。如果您在法国，那里的“.”是千位分隔符而不是基数，则 INSERT 将完全失败。
所有这些字符串转换都会由 Oracle 认为您运行的语言环境进行修改。检查字典视图V$NLS_PARAMETERS。
对于日期/时间值，情况会变得更糟。日期/时间值可能遍布整个地图 - 主要是因为时区。您的数据库服务器位于哪个时区？它认为您正在从哪个时区运行？如果这还不够让您感到困惑，请查看如果将 Oracle 的默认日历从公历更改为泰佛历，会发生什么情况。
我强烈建议你完全摆脱这些数字。
要创建日期或日期时间值，请使用具有完全不变且明确格式的字符串。然后专门对日期值进行赋值、比较和计算，例如：
GOODFMT 常量 VARCHAR2 = 'YYYY-MM-DD HH24:MI:SS.FFF ZZZ'
Good_Time DATE = TO_DATE('2012-02-17 08:07:55.000 EST',好的DFMT）；
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.
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.
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.
All of these conversions with strings are modified by the locale in which Oracle thinks your running. Check dictionary view 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.
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'
Good_Time DATE = TO_DATE ('2012-02-17 08:07:55.000 EST', GOODFMT);