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.
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')
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
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With