ORA-01843 & ldquo; not a valid month & rdquo; and ORA-01861 & ldquo; literal does not match the format string & rdquo;

advertisements
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?


You have to specify format that your timestamp string is in to TO_DATE and TO_TIMESTAMP functions:

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:

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:

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 TIMESTAMP format:

TO_TIMESTAMP('2010-12-14:09:56:53','YYYY-MM-DD:HH24:MI:SS')-TO_TIMESTAMP('2
---------------------------------------------------------------------------
+000000000 00:00:07.000000000