Oracle date format error

Question

I am working on oracle table to pull the data. In one of the query i am using 'where' to filtering between date.

To_Date(VDATU,'DD-MON-YYYY') >= '03-Jun-2012'
AND To_Date(VDATU,'dd-mon-yy') <= '04-Sep-2012'

I am getting error,

ORA-01861: literal does not match format string

I tried using ('dd-mm-yy') but still it is giving the same error. what may the problem??

That error could occur as a result of the To_Date calls, or as a result of the implicit conversion of the strings (e.g. '03-Jun-2012') to dates. — Jeffrey Kemp
– Jeffrey Kemp, Commented Sep 4, 2012 at 4:57

WW. · Accepted Answer · 2012-09-04 02:23:26Z

2

You are trying to convert VDATU (which is presumably a VARCHAR2) into a date.

In one place you have DD-MON-YYYY and in the other you have dd-mon-yy.

Which one is it? VDATU can not be valid for both.

answered Sep 4, 2012 at 2:23

WW.

24.4k15 gold badges99 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yeah it is varchar2. it is in format of '01122011'. what is the correct format to be used for conversion??

Your example is ambiguous, but either 'ddmmyyyy' (1st December) or 'mmddyyyy' (12th Jan).

Nir Alfasi · Accepted Answer · 2012-09-04 02:17:37Z

2

You're comparing a DATE with a String, try instead:

To_Date(VDATU,'DD-MON-YYYY') >= To_Date('03-JUN-2012','DD-MON-YYYY')
AND To_Date(VDATU,'dd-mon-yy') <= To_Date('04-SEP-2012','DD-MON-YYYY')

In case VDATU is already a date, you shouldn't convert it to_date, all you have to do is:

VDATU >= To_Date('03-JUN-2012','DD-MON-YYYY')
AND VDATU <= To_Date('04-SEP-2012','DD-MON-YYYY')

answered Sep 4, 2012 at 2:17

Nir Alfasi

53.6k11 gold badges93 silver badges138 bronze badges