Oracle SQL Developer - Dates sorted incorrectly(?)

Question

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!

What do you get is you run SELECT DUMP(von), TO_CHAR(von, 'YYYY-MM-DD') FROM billinginterval? — MT0
– MT0, Commented Nov 28, 2021 at 11:03
I'm receiving DUMP(VON)=: Typ=12 Len=7: 100,119,1,1,1,1,1 TO_CHAR(von, 'YYYY-MM-DD'): 0019-01-01 It was probably a wrong insert done by someone. — Fanta
– Fanta, Commented Nov 28, 2021 at 11:06

Fanta · Accepted Answer · 2021-11-28 11:04:38Z

1

Apparently the results were reversed because of an incorrect insert in the database. Since

select to_char(von, 'DD-MM-YYYY') from verrechnungszeitraum where id = xxxx;

returns 01-01-0019 as a result. However other rows result in proper years. Closing.

answered Nov 28, 2021 at 11:04

Fanta

375 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user5683823 Over a year ago

What's the point of this "Answer"? MT0 diagnosed the problem correctly, and posted an answer with full details. Your proper reaction to that should be to mark his answer as "Correct Answer", not to post a separate answer of your own. Please delete your answer, and mark the correct answer accordingly.

MT0 Over a year ago

@mathguy I prompted the OP in comments which let them give more details and diagnose the problem themselves. While I was typing up my answer and getting the fiddle to work, they posted this short answer and then shortly after I posted my full answer with a minimal reproducible example and details of what problems to look for in the INSERTs; so their answer did not come after mine.

user5683823 Over a year ago

@MT0 - OK, that makes sense. At this point, though, I would still encourage the OP to close his own answer and to mark yours as the correct answer.

MT0 · Accepted Answer · 2021-11-28 11:15:01Z

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

Collectives™ on Stack Overflow

Oracle SQL Developer - Dates sorted incorrectly(?)

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related