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'