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.

Solution

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().