Order of Dates in Oracle Database I have following table which records employee's attendance in a company.

    CREATE TABLE EMP_ATNDNC
    (
    WORK_DAY    DATE                         NOT NULL,
    EMP_ID      VARCHAR2(20)                 NOT NULL,
    STATUS      VARCHAR2(1)   DEFAULT '0'    NOT NULL,
    TMSTMP      TIMESTAMP,
    RCRD_VER    TIMESTAMP                    NOT NULL,

   CONSTRAINT  EMP_ATNDNC_PK PRIMARY KEY(WORK_DAY,EMP_ID),
   CONSTRAINT  EMP_ATNDNC_FK_DAY FOREIGN KEY(WORK_DAY) REFERENCES SCHOOL_OPEN_RCRD(WORK_DAY),
   CONSTRAINT  EMP_ATNDNC_FK_ID FOREIGN KEY(EMP_ID) REFERENCES EMP_RCRD(EMP_ID)
   );


As of now, it has two records:

    WORK_DAY    EMP_ID     STATUS           TMSTMP                             RCRD_VER
    29-SEP-16     1234       0      27-AUG-16 05.38.46.022000000 PM       27-AUG-16 05.38.46.022000000 PM
    26-AUG-16     9999       0      26-AUG-16 04.50.04.628000000 PM       26-AUG-16 04.50.04.628000000 PM


If I run this query:

    SELECT * FROM EMP_ATNDNC WHERE WORK_DAY BETWEEN TO_DATE('24-AUG-16') AND TO_DATE('30-SEP-16');


I get only this record:

    WORK_DAY    EMP_ID     STATUS           TMSTMP                          RCRD_VER
    29-SEP-16     1234       0      27-AUG-16 05.38.46.022000000 PM     27-AUG-16 05.38.46.022000000 PM


Also If I run this query:

    SELECT * FROM EMP_ATNDNC ORDER BY WORK_DAY ASC;


I get this result:

    WORK_DAY    EMP_ID     STATUS           TMSTMP                             RCRD_VER
    29-SEP-16     1234       0      27-AUG-16 05.38.46.022000000 PM       27-AUG-16 05.38.46.022000000 PM
    26-AUG-16     9999       0      26-AUG-16 04.50.04.628000000 PM       26-AUG-16 04.50.04.628000000 PM


Instead of:

    WORK_DAY    EMP_ID     STATUS           TMSTMP                             RCRD_VER
    26-AUG-16     9999       0      26-AUG-16 04.50.04.628000000 PM       26-AUG-16 04.50.04.628000000 PM
    29-SEP-16     1234       0      27-AUG-16 05.38.46.022000000 PM       27-AUG-16 05.38.46.022000000 PM


Why is this happening?
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.