ORA-01843“不是有效的月份”和ORA-01861“文字与格式字符串不匹配”
ORA-01843“不是有效的月份”和ORA-01861“文字与格式字符串不匹配”
提问于 2010-12-15 02:45:15
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正确的方法是什么？
2010-12-14:09:56:53
minus
2010-12-14:09:56:46在Oracle SQL中？
回答 2
Stack Overflow用户
回答已采纳
发布于 2010-12-15 02:52:33
您必须为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;结果将以天为单位，可以乘以86,400得到秒：
TO_DATE('2010-12-14:09:56:53','YYYY-MM-DD:HH24:MI:SS')-TO_DATE('2010-12-14:09:56
--------------------------------------------------------------------------------
.000081019使用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;结果将采用TIMESTAMP格式：
TO_TIMESTAMP('2010-12-14:09:56:53','YYYY-MM-DD:HH24:MI:SS')-TO_TIMESTAMP('2
---------------------------------------------------------------------------
+000000000 00:00:07.000000000Stack Overflow用户
发布于 2010-12-15 02:52:09
使用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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/4442932
复制相关文章
相似问题
腾讯云开发者
