Order of Dates in Oracle Database
And that is why we never rely on an unspecified date format (or the default in this case).

SQL&gt; create table t(d date);

Table created.

SQL&gt; insert into t values (date'2016-09-29');

1 row created.

SQL&gt; insert into t values (date'2116-08-26');

1 row created.

SQL&gt; select * from t where d BETWEEN TO_DATE('24-AUG-16') AND TO_DATE('30-SEP-16');

D
---------
29-SEP-16

SQL&gt; select * from t order by d;

D
---------
29-SEP-16
26-AUG-16

SQL&gt; alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL&gt; select * from t order by d;

D
-------------------
2016-09-29 00:00:00
2116-08-26 00:00:00


Do not use date format such as DD-MON-RR, as it does not show the century.

Also, do not use TO_DATE without specifying the date format explicitly.

Instead of TO_DATE('24-AUG-16'), use TO_DATE('24-AUG-16', 'DD-MON-RR'), but you still miss the century part. TO_DATE('24-AUG-2016', 'DD-MON-YYYY') is better, but I prefer using the date'2016-08-27 syntax that has a fixed format.