ORA-01861: literal does not match format string for comparing date in Oracle-CodePudding

I have assigned date value into a variable which is 2017-03-15T00:00:00 05:30

and I am comparing with other date whose value is TO_DATE('01-10-16', 'dd-mm-yy')

So here is my query for the same

IF TO_DATE(r.FINAL_SR_DATE, 'dd-mm-yy') >= TO_DATE('01-10-16', 'dd-mm-yy')
  THEN
v_STD_REVISED_AMT_INT := (V_STANDRD_AMT - v_OD_Discount)   (V_STANDRD_AMT - v_OD_Discount) * 
 2.5/100;

But I am getting error as

ORA-01861: literal does not match format string

Please suggest how I can resolve the issue

CodePudding user response：

The string '2017-03-15T00:00:00 05:30' represents a timestamp with time zone. If you want to ignore the time and zone you could just use the date part of the string, but you still need to provide the correct format mask:

TO_DATE(SUBSTR(r.FINAL_SR_DATE, 1, 10), 'YYYY-MM-DD')

You could also keep the time part and ignore the time zone (if you're sure that's a valid thing to do):

TO_DATE(SUBSTR(r.FINAL_SR_DATE, 1, 19), 'YYYY-MM-DD"T"HH24:MI:SS')

although as you are comparing against a date at midnight the time part of your value is a bit irrelevant.

You should also use a 4-digit year for the right hand side of the comparison:

TO_DATE('01-10-2016', 'dd-mm-yyyy')

or more simply a date literal:

DATE '2016-10-01'

so you would end up with:

TO_DATE(SUBSTR(r.FINAL_SR_DATE, 1, 10), 'YYYY-MM-DD') >= DATE '2016-10-01'

If you want to keep the time zone offset as well then you need a different data type:

TO_TIMESTAMP_TZ(r.FINAL_SR_DATE, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')

and then adjust the time zone if necessary, and compare with another timestamp with time zone value, which can also be a literal, e.g.:

TIMESTAMP DATE '2016-10-01 00:00:00 UTC'

TIMESTAMP DATE '2016-10-01 00:00:00 05:30'

so you woudl end up with:

TO_TIMESTAMP_TZ(r.FINAL_SR_DATE, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')
  >= TIMESTAMP DATE '2016-10-01 00:00:00 05:30'

or whatever offset is appropriate for your environment; or preferably with a time zone region instead of an offset.