Search code examples
sqloracledateoracle-sqldeveloper

Oracle SQL Developer - Dates sorted incorrectly(?)

I have a simple query which is supposed to be order the results by the von column in descending order. The column has the datatype of DATE. It is an Oracle database and I'm using Oracle SQL Developer when executing the queries.

Here is the query that I am executing:

select * 
from billinginterval
where id = xxxx or id = yyyy
order by von desc;

and here are the two results I get for this query:

Query Results

Please correct me if I'm wrong, but shouldn't the results appear in the reversed order? As when I execute this query

select von 
from billinginterval
order by von desc;

The result set appears to be in the correct order.

Any help in understanding this would be much appreciated!

Solution

  • If you have the data:

    CREATE TABLE billinginterval (id, von) AS
SELECT 1, DATE '2018-09-01' FROM DUAL;

INSERT INTO billinginterval (id, von)
VALUES (3, TO_DATE('03.01.1919', 'DD.MM.YYYY'));

INSERT INTO billinginterval (id, von)
VALUES (3, TO_DATE('03.01.19', 'DD.MM.YYYY'));

    And setup the session using:

    ALTER SESSION SET NLS_TERRITORY = 'Germany';

    Then the query:

    select b.*,
       TO_CHAR(von, 'YYYY-MM-DD') AS formatted_von
from billinginterval b
order by von desc;

    Will output:

    ID VON FORMATTED_VON
    1 01.09.18 2018-09-01
    2 03.01.19 1919-01-03
    3 03.01.19 0019-01-03

    The row with an id of 2 is probably valid data and should be in that order; however, the row with an id of 3 is probably invalid and someone has used an INSERT like:

    INSERT INTO billinginterval (id, von)
VALUES (3, TO_DATE('03.01.19', 'DD.MM.YYYY'))

    Which has the database has taken the year to be 0019 and not 2019. You should always make sure your data input matches the expected format and do not provide a two-digit year when a four-digit year is expected.

    db<>fiddle here