CODEWITHSUNDEEP
sqloracle-database
banana_99
banana_99

Reputation: 641

Date difference not matching

I have the below query which gives the below output. The '06/01/20' is some corrupted data I need to deal with. Converting it to 'DD/MM/YY' is not an option, I just would like to understand what's happening here.

WITH aux (
    d1,
    d2
) AS (
    SELECT
        '06/01/20',
        '15/01/2021'
    FROM
        dual
)
SELECT
    nvl(to_date(d2, 'DD/MM/YYYY'), sysdate) - to_date(d1, 'DD/MM/YYYY') diff
FROM
    aux;

Output:

DIFF
----
730862

However, if I do the below, the results do not match with what I would expect, the difference between those dates would be

SELECT
    TO_DATE('06/01/20', 'DD/MM/YYYY') d1,
    TO_DATE('15/01/2021', 'DD/MM/YYYY') d2
FROM
    dual

Output:

       D1          D2
---------------------
06-JAN-20   15-JAN-21

--

SELECT
    DATE '2021-01-15' - DATE '2020-01-06' d
FROM
    dual

Output:

D
-
9

Upvotes: 0

Views: 42

Answers (1)

Álvaro González
Álvaro González

Reputation: 146650

I suggest you set a default date format that allows to see full dates. Many Oracle clients have such settings and you can also change it for current session:

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

When you do so you'll realise that TO_DATE('06/01/20', 'DD/MM/YYYY') produces 0020-01-06 rather than 2020-01-06.

An average year has 365.25 days and 730862 / 365,25 equals 2001 ;-)

Upvotes: 1

Related Questions