CODEWITHSUNDEEP
sqloracle-databaseoracle10gora-01861
mcgoncu
mcgoncu

Reputation:

ORA-01861: literal does not match format string

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?

Upvotes: 39

Views: 492499

Answers (6)

vaati
vaati

Reputation: 162

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.

Upvotes: 1

Pratap Kumar Panda
Pratap Kumar Panda

Reputation: 171

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.

Upvotes: 0

Vijay
Vijay

Reputation: 19

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

Upvotes: 1

sweta
sweta

Reputation: 19

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

Upvotes: 1

Christian13467
Christian13467

Reputation: 5614

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.

Upvotes: 40

OMG Ponies
OMG Ponies

Reputation: 332731

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')

Upvotes: 9

Related Questions