SQL ORA-01843“无效月份”和 ORA-01861“文字与格式字符串不匹配”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4442932/
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
ORA-01843 "not a valid month" and ORA-01861 "literal does not match format string"
提问by Moeb
SQL> select to_timestamp('2010-12-14:09:56:53') - to_timestamp('2010-12-14:09:56:46') from dua
l;
select to_timestamp('2010-12-14:09:56:53') - to_timestamp('2010-12-14:09:56:46') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> select to_date('2010-12-14:09:56:53') - to_date('2010-12-14:09:56:46') from dual;
select to_date('2010-12-14:09:56:53') - to_date('2010-12-14:09:56:46') from dual
*
ERROR at line 1:
ORA-01861: literal does not match format string
What is the correct way to do a
做一个的正确方法是什么
2010-12-14:09:56:53
minus
2010-12-14:09:56:46
in Oracle SQL?
在 Oracle SQL 中？
回答by vls
You have to specify format that your timestamp string is in to TO_DATEand TO_TIMESTAMPfunctions:
您必须指定时间戳字符串所在的格式TO_DATE和TO_TIMESTAMP功能：
select
to_date('2010-12-14:09:56:53', 'YYYY-MM-DD:HH24:MI:SS') -
to_date('2010-12-14:09:56:46', 'YYYY-MM-DD:HH24:MI:SS')
from dual;
Result will be in days, which you can multiply by 86,400 to get seconds:
结果将以天为单位，您可以乘以 86,400 得到秒：
TO_DATE('2010-12-14:09:56:53','YYYY-MM-DD:HH24:MI:SS')-TO_DATE('2010-12-14:09:56
--------------------------------------------------------------------------------
.000081019
Using TO_TIMESTAMP:
使用TO_TIMESTAMP：
select
to_timestamp('2010-12-14:09:56:53', 'YYYY-MM-DD:HH24:MI:SS') -
to_timestamp('2010-12-14:09:56:46', 'YYYY-MM-DD:HH24:MI:SS')
from dual;
Result will be in TIMESTAMPformat:
结果将采用以下TIMESTAMP格式：
TO_TIMESTAMP('2010-12-14:09:56:53','YYYY-MM-DD:HH24:MI:SS')-TO_TIMESTAMP('2
---------------------------------------------------------------------------
+000000000 00:00:07.000000000
回答by Conrad Frix
Use to_timesatmp
使用 to_timesatmp
select TO_TIMESTAMP('2010-12-14:09:56:53', 'YYYY-MM-DD:HH24:MI:SS.FF')
- TO_TIMESTAMP('2010-12-14:09:56:46', 'YYYY-MM-DD:HH24:MI:SS.FF')
from dual
