Date field literal does not match format string

Question

I have a field called program_start_date that is formatted as YYYYMM. I need to determine if this date occurs 5 years ago or less, if it occurs more than 5 years ago, or if it is blank.

I have the following case statement in my SQL query:

CASE
   WHEN floor(MONTHS_BETWEEN(sysdate, program_start_date)/12) <= 5 THEN '5 years or less'
   WHEN floor(MONTHS_BETWEEN(sysdate, program_start_date)/12) > 5 THEN '6+ years'
   WHEN program_start_date is null THEN 'Blank'
   ELSE program_start_date
END

I'm getting the following error message:

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 tried inserting to_date() in the first two cases, but I'm still getting the same error message. How can I resolve this issue?

Can you please share the failing expression with the to_date call? — Mureinik
I tried this but it didn't work: WHEN floor(MONTHS_BETWEEN(sysdate, to_date(program_start_date))/12) <= 5 THEN '5 years or less' WHEN floor(MONTHS_BETWEEN(sysdate, to_date(program_start_date))/12) > 5 THEN '6+ years' — b00kgrrl

Mureinik Mureinik · Accepted Answer · 2015-01-13T20:51:36

Your expression implicitly assumes that the database's default nls_date_format matches that of the column, which as you saw, simply isn't true - and even if it was, it's a bad practice to make that assumption, as it makes your code as fragile as a butterfly wing.

Instead, you should explicitly state the format, using the to_date function:

CASE
   WHEN floor(MONTHS_BETWEEN(sysdate, TO_DATE(program_start_date, 'YYYMM'))/12) <= 5 THEN '5 years or less'
   WHEN floor(MONTHS_BETWEEN(sysdate, TO_DATE(program_start_date, 'YYYMM'))/12) > 5 THEN '6+ years'
   WHEN program_start_date is null THEN 'Blank'
   ELSE program_start_date
END

Date field literal does not match format string

1 Answers