Cast as date - literal does not match format string

Question

i have this select :

 select P.DT03_DT as PROPOSAL_DATE,
        p.dt02_dt as DATE_OF_JUDGEMENT,
        P.DT05_DT as END_DATE,
        cast('20140704' as date) as Dt  

       from imxdb.t_intervenants i    
         join imxdb.g_indivparam p
       on i.refindividu = p.refindividu

        where p.type = 'BANCRUPTCY'    
         AND (to_char(p.dt02_dt, 'yyyymmdd') ='20140704' or
         to_char(P.DT05_DT, 'yyyymmdd') = '20140704')

and this gives me error :

ORA-01861: literal does not match format string 01861. 00000 - "literal does not match format string"

but when i just commment the line "cast('20140704' as date) as Dt " its working fine, why?

Thank you

Lalit Kumar B Lalit Kumar B · Accepted Answer · 2014-09-23T08:28:17

The issue is with the NLS_DATE_FORMAT.

Let me explain with a small example :

My NLS_DATE_FORMAT is DD-MON-RR

SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';

VALUE
----------------------------------------------------------------
DD-MON-RR

SQL>

I can verify that in SQL*Plus :

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
23-SEP-14

SQL>

Now, if I use to CAST a date literal in unknown format, I will get an error :

SQL> SELECT CAST('20140704' AS DATE) AS DT FROM DUAL;
SELECT CAST('20140704' AS DATE) AS DT FROM DUAL
            *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL>

So, I will give the required NLS_DATE_FORMAT, and it should be fine :

SQL> SELECT CAST('23-sep-14' AS DATE) AS DT  FROM DUAL;

DT
---------
23-SEP-14

SQL>

Other alternatives,

Change the NLS_DATE_FORMAT
Alter it at session level.
Alternatively, you can use TO_DATE and use proper `FORMAT

It will OVERRIDE the locale-specific NLS settings.

SQL> select to_date('20140704', 'YYYYMMDD') from dual;

TO_DATE('
---------
04-JUL-14

If you want to display in your desired format, then use TO_CHAR.

Cast as date - literal does not match format string

2 Answers