ERROR:: "literal does not match format string" 1

[joeroe3380]

Hi group ,
im trying to extract table from oracle table using select ..
but i encounter the following error...

SQL> SELECT * FROM TABLE_RPT WHERE CYC_DT = '20030303' AND ROWNUM<10;
SELECT * FROM TABLE_RPT WHERE CYC_DT = '20030303' AND ROWNUM<10
*
ERROR at line 1:
ORA-01861: literal does not match format string
----------------------------------------------------------

but when i change the CYC_DT format to be '03-JAN-2003' it runs fine..

please advice how to go about solving this error..and the reason for the error message...

Thanks for ur help !!

regards,
Suzzann'e

 

[SantaMufasa]

Joe,

The reason '20030303' failed is because Oracle checks the left operand of the relation comparison, determines it is a date and proceeds to try to convert '20030303' into a date. Since '20030303' does not match your default date format of 'DD-MON-YYYY', it throws an error. For those same reasons, your string '03-JAN-2003' worked because it can successfully apply your system's default date mask to your comparison string.

To get your first value to work successfully, you can change your code to the following:

SELECT * FROM TABLE_RPT WHERE trunc(CYC_DT) = to_date('20030303','yyyymmdd') AND ROWNUM<10;

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:35 (30Sep04) UTC (aka "GMT" and "Zulu"), 12:35 (30Sep04) Mountain Time)