Oracle Error – Literal Does Not Match Format String

oracle

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

Best Answer

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'));
Related Question