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    gt  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

User · Answer

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

User · Answer

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

User · Answer

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    gt  TO DATE   07 09 2008  09 43 00    DD MM YYYY HH24 MI SS    ORDER BY ALARM DATETIME DESC

User · Answer

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

User · 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

User · Answer

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  gt    01FEB2021   Just did something like this to fix it  create view myview as  select   from x where x initialDate  gt   TO DATE  2021-02-01    YYYY-MM-DD    I think the problem is EF date configuration is not the same as Oracle s

[sql] ORA-01861: literal does not match format string

Examples related to sql

Examples related to oracle

Examples related to oracle10g

Examples related to ora-01861