Oracle SQL query for Date format

Question

I always get confused with date format in ORACLE SQL query and spend minutes together to google, Can someone explain me the simplest way to tackle when we have different format of date in database table ?

for instance i have a date column as ES_DATE, holds data as 27-APR-12 11.52.48.294030000 AM of Data type TIMESTAMP(6) WITH LOCAL TIME ZONE.

I wrote simple select query to fetch data for that particular day and it returns me nothing. Can someone explain me ?

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

or

select * from table where es_date = '27-APR-12';

Answer 1

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'