1

I am trying to convert one of the varchar2 column to date in oracle using the below query. 

SELECT *
FROM login
WHERE to_date(END_DATE,'DD-MM-YY') < to_date(TRUNC(SYSDATE)-90,'DD-MM-YY');

I am converting the both side to date with a common formatter. But still I am getting the below error while executing this query.

 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

Can you please help me to sort out this problem?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Pushpa
  • 109
  • 2
  • 9
  • Can you show sample data? – Jens May 28 '15 at 08:38
  • @Jens I have shared the query which I am trying to execute. My column will be having value/data like... 14-Mar-2015.And the query Select SYSDATE from dual is giving me data in 28-05-15 format. So I am formaating both the date to DD-MM-YY. – Pushpa May 28 '15 at 08:44
  • Try to use `MON` instat of `MM` in to to_date – Jens May 28 '15 at 08:47
  • Does SELECT end_date FROM login shows the date as 14-Mar-2015? IF so you need MON to format string instead MM. – kayess May 28 '15 at 08:48
  • @Pushpa why did you tag `sqlserver2008`? The error clearly shows you use `Oracle`. – Lalit Kumar B May 28 '15 at 09:00

1 Answers1

1

to_date(END_DATE,'DD-MM-YY')

  • First of all, it is a bad design to store DATE as STRING. Date should always be stored as DATE data type, there is no reason to store it as characters.

  • If your data is stored as 14-Mar-2015 then why are you using the 'DD-MM-YY' format. Clearly the formats doesn't match. You should use proper format model.

For example,

TO_DATE(14-Mar-2015,'DD-Mon-YYYY','NLS_DATE_LANGUAGE=ENGLISH')

to_date(TRUNC(SYSDATE)-90,'DD-MM-YY')

  • This makes no sense. TRUNC on DATE would return you DATE after truncating the time portion.

  • Never ever use TO_DATE on DATE. It will implicitly convert it into string and then back to date using locale-specific NLS format. See a detailed explanation in my previous answer here https://stackoverflow.com/a/29559609/3989608

Your modified query would look like:

SELECT *
FROM login
WHERE to_date(END_DATE,'DD-Mon-YYYY','NLS_DATE_LANGUAGE=ENGLISH') < TRUNC(SYSDATE)-90;
Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • I have changed my query to Select * from login where TO_DATE(END_DATE,'DD-Mon-YYYY','NLS_DATE_LANGUAGE=ENGLISH') < TRUNC(SYSDATE) -90 ; Still getting the same error – Pushpa May 28 '15 at 09:03
  • @Pushpa Why did you store date as string? Are all the values in the same format like `14-mar-2015`? Post some sample data. – Lalit Kumar B May 28 '15 at 09:05
  • what is the datatype of your end_date column? If it's DATE or TIMESTAMP, then as Lalit mentioned, you wouldn't put to_date around it. – Boneist May 28 '15 at 09:05
  • @Boneist Actually OP said it's varchar2 in the question. So, I assume that there are values with different format which is causing the issue. i would not allow such a design ;-) – Lalit Kumar B May 28 '15 at 09:06
  • @Pushpa Execute the follwoing query and let me know if it throws error `SELECT to_date(END_DATE,'DD-Mon-YYYY','NLS_DATE_LANGUAGE=ENGLISH') FROM login` – Lalit Kumar B May 28 '15 at 09:08
  • @LalitKumarB this will work fine.. I believe the problem is coming because the left hand side date format and right hand side date format is not matching.. – Pushpa May 28 '15 at 09:20
  • @Pushpa DATE doesn't have any format. What you see is only for display for human readable format. – Lalit Kumar B May 28 '15 at 09:29