Oracle Time Difference between SYSDATE and Date From Column
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.
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() .
上一篇: EF6 Oracle时间戳和日期