ORA-01843“不是有效的月份”和 ORA-01861“文字与格式字符串不匹配”

Question

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

执行 a 的正确方法是什么？

2010-12-14:09:56:53
minus
2010-12-14:09:56:46

在 Oracle SQL 中

Answer 1

您必须指定时间戳字符串的格式 <代码>TO_DATE 和 < code>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.000000000

Answer 2

使用 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