Oracle SQL query for Date format

to_date() returns a date at 00:00:00, so you need to “remove” the minutes from the date you are comparing to:

select * 
from table
where trunc(es_date) = TO_DATE('27-APR-12','dd-MON-yy')

You probably want to create an index on trunc(es_date) if that is something you are doing on a regular basis.

The literal '27-APR-12' can fail very easily if the default date format is changed to anything different. So make sure you you always use to_date() with a proper format mask (or an ANSI literal: date '2012-04-27')

Although you did right in using to_date() and not relying on implict data type conversion, your usage of to_date() still has a subtle pitfall because of the format 'dd-MON-yy'.

With a different language setting this might easily fail e.g. TO_DATE('27-MAY-12','dd-MON-yy') when NLS_LANG is set to german. Avoid anything in the format that might be different in a different language. Using a four digit year and only numbers e.g. 'dd-mm-yyyy' or 'yyyy-mm-dd'

Leave a Comment