ORA-01861: literal does not match format string

Question

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?

Haruspicy

Try running this statement using SQL Plus to see what it has to say about this. If SQLPlus can execute it, ado.net should not have any problem. – Haddad

Answer 1

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.

Blaineblainey

Answer 2

The error means that you tried to enter a literal with a format string, but the length of the format string was not the same length as the literal.

One of these formats is incorrect:

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

Soakage

What use is an answer where you put up an example and say that half of it is incorrect? – Confidante

Answer 3

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_char (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 

Beckybecloud

Answer 4

Just before executing the query: alter session set NLS_DATE_FORMAT = "DD.MM.YYYY HH24:MI:SS"; or whichever format you are giving the information to the date function. This should fix the ORA error

Campion

Code should never depend on a NLS_DATE_FORMAT. That parameter is useful for configuring how to display data, but it shouldn't be used for processing. – Harmattan

Answer 5

A simple view like this was giving me the ORA-01861 error when executed from Entity Framework:

create view myview as 
select * from x where x.initialDate >= '01FEB2021'

Just did something like this to fix it:

create view myview as 
select * from x where x.initialDate >= TO_DATE('2021-02-01', 'YYYY-MM-DD')

I think the problem is EF date configuration is not the same as Oracle's.

Maestas

Answer 6

If you are using JPA to hibernate make sure the Entity has the correct data type for a field defined against a date column like use java.util.Date instead of String.

Pike