When I try to execute this snippet:
There is no problem with database connection because I can execute basic SQL commands.
What is the problem with this statement?
4 Answers 4
Remove the TO_DATE in the WHERE clause
and change the code to
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.
I was given a SELECT statement that is running in a Crystal Report to investigate a data problem but when I try to run it against the database I get the error:
ORA-01861: literal does not match format string 01861. 00000 — «literal does not match format string» *Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the «FX» modifier has been toggled on, the literal must match exactly, with no extra whitespace. *Action: Correct the format string to match the literal.
I think it has to do with the Date comparisons but I am not sure what is wrong. I know nothing about oracle and since the error message does not specify the line the error is on I am only guessing at what might be the problem. Can anyone point me to what is causing this problem?
Learn the cause and how to resolve the ORA-01861 error message in Oracle.
Description
When you encounter an ORA-01861 error, the following error message will appear:
- ORA-01861: literal does not match format string
Cause
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.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
Re-enter the literal so that it matches the format string.
For example, if you tried to execute the following statement:
You would receive the following error message:
You could correct the SQL statement as follows:
As a general rule, if you are using the TO_DATE function, TO_TIMESTAMP function, TO_CHAR function, and similar functions, make sure that the literal that you provide matches the format string that you’ve specified.