Oracle Time Difference between SYSDATE and Date From Column

Question

I'm very new to Oracle. I have this problem, I got 365 days difference while these 2 dates' difference is are only one day.

This is my SYSDATE Query Output:

SELECT to_date(SYSDATE,'yyyy-MM-dd') FROM LOGINRECORDS

15-OCT-11

This is my date from column Query Output:

SELECT to_date(LoginDate,'yyyy-mm-dd') FROM LOGINRECORDS WHERE LoginRecordId = '1000001'

15-OCT-10

And when I run this query :

SELECT (to_date(SYSDATE,'yyyy-MM-dd') - to_date(LoginDate,'yyyy-MM-dd')) difference FROM LOGINRECORDS WHERE LoginRecordId = '1000001'

I got this:

365

This is my table description :

CREATE TABLE LOGINRECORDS
(
    LoginRecordId NUMBER GENERATED ALWAYS AS IDENTITY START WITH 1000000 INCREMENT BY 1,    
    LoginDate DATE,
    patientUserId NUMBER
)

Hope you guys willing to help, Tq in advance.

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-10-11T15:20:50

This expression:

to_date(SYSDATE, 'yyyy-MM-dd')

doesn't make sense. SYSDATE is already a date. So, this expression converts SYSDATE to a string, using whatever local settings are on your system. Then, it converts that result to a date, using the format 'yyyy-MM-dd'. For many values of the local settings, this would simply fail.

If you want the difference, then do something like this:

SELECT (trunc(SYSDATE) - trunc(LoginDate)) as difference
FROM LOGINRECORDS
WHERE LoginRecordId = '1000001';

Note that in Oracle, the DATE data type has a time component, hence the use of trunc(). If you know that LoginDate has no time component, then that part does not require trunc().

Oracle Time Difference between SYSDATE and Date From Column

1 Answers