Simple query with date type requires explicit conversion to_date of date
All dates in Oracle have a time component.

I'm pretty sure you want all rows between the 13th and the 15th included. However, when you write:

BETWEEN to_date('13-02-2011', 'DD-MM-YYYY') AND TO_DATE('15-02-2011', 'DD-MM-YYYY')


you select only all date up to the 15-02-2011 at midnight. All rows later that day won't be selected.

What is happening is that when you use TO_DATE on a date, it will be converted to VARCHAR2 and then converted back to a date. The net effect will be a truncation to your date format as shown:

SQL&gt; alter session set nls_date_format='dd/mm/yyyy';

Session altered.

SQL&gt; select to_char(to_date(sysdate), 'dd/mm/yyyy hh24:mi:ss') trunc,
  2         to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') not_trunc
  3    from dual;

TRUNC               NOT_TRUNC
------------------- -------------------
23/10/2012 00:00:00 23/10/2012 15:15:32


So your two clauses are not equivalent at all. The second one selects the whole day feb, 15th whereas the first one only selects the first second of the day.

I suggest you use the following constructions when writing date range:

WHERE dat &gt;= to_date('13-02-2011', 'DD-MM-YYYY')
  AND dat &lt; to_date('16-02-2011', 'DD-MM-YYYY')


Also don't use MON or DAY format in your code since they are dependent upon language. Don't use YY since this can be confusing (ever heard of the Y2k bug?). Both formats are ok to display information to your users of course.