Question User Question User - 5 months ago 20
SQL Question

oracle to_date retruns some error in custom query

Oracle DB

START_DATE |END_DATE
--------------------|-------------------
2016-02-01 00:00:00 |2016-02-29 23:55:00
2016-02-01 00:00:00 |2016-02-29 23:55:00
2016-02-01 00:00:00 |2016-02-29 23:55:00
2016-02-01 00:00:00 |2016-02-29 23:55:00
2016-02-01 00:00:00 |2016-02-29 23:55:00


Query

`select * from VM_REPORT_TEMP_US where to_date(START_DATE,'YYYY-MM-DD HH24:MI:SS') >= '2016-02-01 00:00:00' AND to_date(END_DATE,'YYYY-MM-DD HH24:MI:SS') <= '2016-`02-24 24:59:00'`


Im trying to run this query but im getting some error. Can some one know where im going in this query ?

Im getting the below error


SQL Error [1861] [22008]: ORA-01861: literal does not match format string

java.sql.SQLDataException: ORA-01861: literal does not match format string

Answer

Do NOT use to_date() with a DATE column. That first converts the date value into a varchar value just to convert that back to a date which it was to begin with.

to_date() expects a varchar value, so Oracle first converts the DATE value into a varchar value using the current NLS settings. Then it tries to convert that varchar back into a date, using the format mask you supplied which most probably doesn't match the default NLS format you have and therefor you get an error.

You should also use proper date values in the condition rather then strings that are (again) implicitly converted to a DATE based on the current NLS settings:

select * 
from VM_REPORT_TEMP_US 
where START_DATE >= timestamp '2016-02-01 00:00:00' 
  AND END_DATE <= timestamp '2016-02-24 23:59:00'

Note that the hour 24 is invalid in an ISO timestamp literal.

If you want to provide the date/timestamp value in a format other then the ISO format, you need to use to_date() for those:

select * 
from VM_REPORT_TEMP_US 
where START_DATE >= to_date('01.02.2016 00:00:00', 'dd.mm.yyyy hh24:mi:ss') 
  AND END_DATE <= to_date('24.02.2016 23:59:59', 'dd.mm.yyyy hh24:mi:ss')