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'));
3 Answers 3
Oracle doesn't understand the literal date format strings, as they are not in the same format that your client is configured for. You need to use the TO_DATE function to tell Oracle the format of the date string you are presenting it with, like so:
AND (ACTIVITY.ACTIVITY_START_DATE >= TO_DATE('2014-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS'))
AND (ACTIVITY.ACTIVITY_START_DATE < TO_DATE('2014-02-06 00:00:00','YYYY-MM-DD HH24:MI:SS')));
You could also represent your dates using ANSI syntax (seeing as they have no time component):
AND (ACTIVITY.ACTIVITY_START_DATE >= DATE'2014-02-05')
AND (ACTIVITY.ACTIVITY_START_DATE < DATE'2014-02-06'));
A default installation of Oracle Database sets the default DATE format to DD-MON-YYYY.
And you were using a different format in your sql query. Either you can use to_date function or simply change the date format model to DD-MON-YYYY. .
If ACTIVITY.ACTIVITY_START_DATE has the DATE data type, the comparison should look something like this (you cannot always compare dates with stings in the Oracle Database):
...ACTIVITY.ACTIVITY_START_DATE >= TO_DATE('2014-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')