CODEWITHSUNDEEP
sqldateoracle11gsql-order-by
Aakash
Aakash

Reputation: 1029

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?

Upvotes: 1

Views: 104

Answers (2)

Alex Poole
Alex Poole

Reputation: 191265

You seem to have dates which are not in the current century, possibly through them being inserted with an incorrect format model. (There isn't really any reason to still be using two-digit years any more...)

To demonstrate, with you table definition:

insert into EMP_ATNDNC
values (to_date('26-AUG-16', 'DD-MON-RRRR'), 9999, 0,
  to_timestamp('26-AUG-16 04.50.04.628000000 PM', 'DD-MON-RR HH.MI.SS.FF AM'),
  to_timestamp('26-AUG-16 04.50.04.628000000 PM', 'DD-MON-RR HH.MI.SS.FF AM'));

insert into EMP_ATNDNC
values (to_date('29-SEP-16', 'DD-MON-YYYY'), 1234, 0,
  to_timestamp('27-AUG-16 05.38.46.022000000 PM', 'DD-MON-RR HH.MI.SS.FF AM'),
  to_timestamp('27-AUG-16 05.38.46.022000000 PM', 'DD-MON-RR HH.MI.SS.FF AM'));

alter session set nls_date_format = 'DD-MON-RR';
alter session set nls_timestamp_format = 'DD-MON-RR HH:MI:SS.FF3 AM';

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

WORK_DAY  EMP_ID               S TMSTMP                    RCRD_VER                
--------- -------------------- - ------------------------- -------------------------
26-AUG-16 9999                 0 26-AUG-16 04:50:04.628 PM 26-AUG-16 04:50:04.628 PM

WORK_DAY  EMP_ID               S TMSTMP                    RCRD_VER                
--------- -------------------- - ------------------------- -------------------------
29-SEP-16 1234                 0 27-AUG-16 05:38:46.022 PM 27-AUG-16 05:38:46.022 PM
26-AUG-16 9999                 0 26-AUG-16 04:50:04.628 PM 26-AUG-16 04:50:04.628 PM

If you look at the two insert statement one is converting the two-digit year using RRRR, which makes some (usually helpful) assumptions about the century, in this case treating '16' as '2016'; the second is using YYYY which is harsher.

If you query the actual dates using a full-year format model you'll see the difference:

select to_char(work_day, 'SYYYY-MM-DD') as work_day, emp_id from emp_atndnc order by work_day;

WORK_DAY    EMP_ID             
----------- --------------------
 0016-09-29 1234                
 2016-08-26 9999

The problem isn't really with your query (though that is using two-digit years and implicit conversions relying on your session's NLS settings too, which @TimBiegeleisen pointed out isn't a good idea).

The real problem is with the data. You may be able to correct it - if you are also willing to make assumptions, e.g. adding 2000 years to anything before this century.

But really you also need to find out where that bad data came from, and stop it happening. It's likely you have an insert that is relying on implicit date conversion from a string, and different users or applications have different NLS settings - specifically NLS_DATE_FORMAT.

You should never rely on implicit data conversions that rely on NLS settings. And you shouldn't really be using two-digit years any more, though that may be out of your control.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

The second date in your BETWEEN clause has the year specified as a two digit number:

BETWEEN TO_DATE('24-AUG-2016') AND TO_DATE('30-SEP-16');
                                                   ^^

Assuming that your default date format is dd-mon-yyyy making the following change might work:

BETWEEN TO_DATE('24-AUG-2016') AND TO_DATE('30-SEP-2016');

However, as the documentation for TO_DATE mentions:

It is good practice always to specify a format mask (fmt) with TO_DATE

So ideally you should explicitly mention the date format you are using, e.g. as in this query:

SELECT *
FROM EMP_ATNDNC
WHERE WORK_DAY BETWEEN TO_DATE('24-AUG-2016', 'dd-mon-yyyy') AND
                       TO_DATE('30-SEP-2016', 'dd-mon-yyyy')

Upvotes: 1

Related Questions