CODEWITHSUNDEEP
sqloracle-databasedate
Hatik
Hatik

Reputation: 1159

SQL Date difference returns wrong result if date entered manually

I am trying to find the difference between two dates by 

SELECT TO_DATE(SYSDATE, 'DD-MM-YYYY') - TO_DATE('27-06-2017', 'DD-MM-YYYY') FROM DUAL;

it returns -730492, which is wrong as it should be -5. I have tried

SELECT TO_DATE(SYSDATE, 'DD-MM-YYYY') FROM DUAL; --(1)
SELECT TO_DATE('27-06-2017', 'DD-MM-YYYY') FROM DUAL; -- (2)

the (1) returns 22-06-2017, and the (2) returns 27-06-2017 as it should. Also if I try 

SELECT TO_DATE(SYSDATE, 'DD-MM-YYYY') - TO_DATE(SYSDATE + 5, 'DD-MM-YYYY') FROM DUAL;

It will return the correct value -5. Why when I try to find difference between manually entered data and the system generated one, I get the different result?

Upvotes: 2

Views: 819

Answers (1)

SandPiper
SandPiper

Reputation: 2906

There is no need to wrap SYSDATE in TO_DATE(). It is already a date. What you are doing is implicit type conversion, and it is not needed here.

It takes your original statement: TO_DATE(SYSDATE, 'DD-MM-YYYY')

And it is turning it into this: TO_DATE(TO_CHAR(SYSDATE), 'DD MONTH YYYY')

Try replacing it with this line instead:

SELECT SYSDATE - TO_DATE('27-06-2017', 'DD-MM-YYYY') FROM DUAL;

Upvotes: 3

Related Questions