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?
asked Sep 7, 2009 at 7:00
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.
answered Sep 7, 2009 at 7:09
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')
answered Sep 7, 2009 at 7:11
OMG PoniesOMG Ponies
321k79 gold badges516 silver badges499 bronze badges
1
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
Pete Carter
2,6913 gold badges23 silver badges34 bronze badges
answered Jun 4, 2012 at 6:48
swetasweta
191 bronze badge
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
answered Sep 9, 2016 at 21:25
VijayVijay
191 bronze badge
1
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.
answered Feb 27, 2021 at 2:15
vaativaati
1522 silver badges12 bronze badges
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.
answered Dec 23, 2020 at 11:21
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?
asked Sep 7, 2009 at 7:00
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.
answered Sep 7, 2009 at 7:09
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')
answered Sep 7, 2009 at 7:11
OMG PoniesOMG Ponies
321k79 gold badges516 silver badges499 bronze badges
1
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
Pete Carter
2,6913 gold badges23 silver badges34 bronze badges
answered Jun 4, 2012 at 6:48
swetasweta
191 bronze badge
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
answered Sep 9, 2016 at 21:25
VijayVijay
191 bronze badge
1
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.
answered Feb 27, 2021 at 2:15
vaativaati
1522 silver badges12 bronze badges
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.
answered Dec 23, 2020 at 11:21
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?
asked Sep 7, 2009 at 7:00
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.
answered Sep 7, 2009 at 7:09
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')
answered Sep 7, 2009 at 7:11
OMG PoniesOMG Ponies
321k79 gold badges516 silver badges499 bronze badges
1
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
Pete Carter
2,6913 gold badges23 silver badges34 bronze badges
answered Jun 4, 2012 at 6:48
swetasweta
191 bronze badge
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
answered Sep 9, 2016 at 21:25
VijayVijay
191 bronze badge
1
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.
answered Feb 27, 2021 at 2:15
vaativaati
1522 silver badges12 bronze badges
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.
answered Dec 23, 2020 at 11:21
Last Modified Date: 24 Aug 2022
Issue
When using the DATE() function in a calculated field with an Oracle data source, the following error might occur:
ORA-01861: literal does not match format string
Environment
- Tableau Desktop
- Oracle
Resolution
Use the DATEPARSE() function instead of the DATE() function.
For example, if the DATE() calculation is
DATE( STR ([Year]) + "-" + STR([Month]) + "-1" )
The matching DATEPARSE() calculation would be
DATEPARSE("yyyy-MM-dd", (STR ([Year]) + "-" + STR([Month]) + "-1" ))
Cause
This error occurs when a literal is entered with a format string, but the length of such format string is not the same as the literal. This can sometimes occur in Tableau Desktop when using the DATE() function along with an Oracle database.
Additional Information
For more information about this error message, see Oracle / PLSQL: ORA-01861 Error Message.
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?
SELECT ACTDET.GROSS_VOLUME,
ACTDET.NET_VOLUME,
ACTIVITY.SHIPPER_ID,
ACTIVITY.ACTIVITY_START_DATE,
SHIPPER.NAME_1,
TANK.TANK_PRODUCT_CODE,
PRODUCT.DESCRIPTION_2,
SITE.NAME_1,
SITE.NAME_2
FROM G3USER.ACTIVITY ACTIVITY,
G3USER.ACTDET ACTDET,
G3USER.SHIPPER SHIPPER,
G3USER.TANK TANK,
G3USER.SITE SITE,
G3USER.PRODUCT PRODUCT
WHERE ((ACTIVITY.TERMINAL_ID=ACTDET.TERMINAL_ID) AND (ACTIVITY.BOL_NUMBER=ACTDET.BOL_NUMBER))
AND ((ACTIVITY.TERMINAL_ID=SHIPPER.TERMINAL_ID) AND (ACTIVITY.SHIPPER_ID=SHIPPER.SHIPPER_ID))
AND ((ACTDET.TERMINAL_ID=TANK.TERMINAL_ID) AND (ACTDET.TANK_ID=TANK.TANK_ID))
AND (ACTDET.TERMINAL_ID=SITE.TERMINAL_ID)
AND ((TANK.TERMINAL_ID=PRODUCT.TERMINAL_ID AND (TANK.TANK_PRODUCT_CODE=PRODUCT.PRODUCT_CODE))
AND (ACTIVITY.ACTIVITY_START_DATE >= '2014-02-05 00:00:00') AND (ACTIVITY.ACTIVITY_START_DATE < '2014-02-06 00:00:00'));