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:
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!
CodePudding user response:
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.
CodePudding user response:
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