ORA-01861: literal does not match format string

sql oracle oracle10g ora-01861

mcgoncu · Sep 7, 2009 · Viewed 414.7k times · Source

When I try to execute this snippet:

cmd.CommandText = "SELECT alarm_id,definition_description,element_id,
    TO_CHAR (alarm_datetime, 'YYYY-MM-DD HH24:MI:SS'),severity,
    problem_text,status FROM aircom.alarms 
    WHERE status = 1 and 
    TO_DATE (alarm_datetime,'DD.MM.YYYY HH24:MI:SS') > TO_DATE ('07.09.2008 
    09:43:00', 'DD.MM.YYYY HH24:MI:SS') 
    order 
    by ALARM_DATETIME desc";

I get:

ORA-01861: literal does not match format string

There is no problem with database connection because I can execute basic SQL commands.

What is the problem with this statement?

Answer

Remove the TO_DATE in the WHERE clause

TO_DATE (alarm_datetime,'DD.MM.YYYY HH24:MI:SS')

and change the code to

alarm_datetime

The error comes from to_date conversion of a date column.

Added Explanation: Oracle converts your alarm_datetime into a string using its nls depended date format. After this it calls to_date with your provided date mask. This throws the exception.

ORA-01861: literal does not match format string

Answer

Related questions