How can I subtract the system date?

Question

SELECT SYSDATE - TO_DATE('1-02-2009', 'DD-MM-YYYY')
FROM DUAL;

But doesn't work :/

OUTPUT :

3981.202453703703703703703703703703703704

may be this helpful to you URl :-https://stackoverflow.com/questions/1646001/how-can-i-get-the-number-of-days-between-2-dates-in-oracle-11g — Kandy, Dec 27 '19 at 05:03
"But doesn't work :/" is not a description of a specific problem or question. — sticky bit, Dec 27 '19 at 05:06
It does exactly work. I guess your expectation that is wrong. When you subtract 2 dates in Oracle the result is a floating point number representing the number of days between them. Where the whole part of the number is the complete number of days and the fractional part is the hours,minuets, seconds represented and a fractional part of a day. — Belayer, Dec 27 '19 at 05:36

score 2 · Answer 1 · answered Dec 27 '19 at 07:18

You will get a difference in number of days when you subtract two dates in Oracle.

To achieve number of hours, minutes or seconds, You will need to multiply them with number of hours in a day, number of minutes in a day or number of seconds in a day respectively as follows:

SQL> SELECT
  2      ROUND(SYSDATE - TO_DATE('01-02-2009', 'DD-MM-YYYY'), 2) AS DIFF_IN_DAYS,
  3      ROUND((SYSDATE - TO_DATE('01-02-2009', 'DD-MM-YYYY')) * 24, 2) AS DIFF_IN_HOURS,
  4      ROUND((SYSDATE - TO_DATE('01-02-2009', 'DD-MM-YYYY')) * 24 * 60, 2) AS DIFF_IN_MINUTES,
  5      ROUND((SYSDATE - TO_DATE('01-02-2009', 'DD-MM-YYYY')) * 24 * 60 * 60, 2) AS DIFF_IN_SECONDS
  6  FROM
  7      DUAL;

DIFF_IN_DAYS DIFF_IN_HOURS DIFF_IN_MINUTES DIFF_IN_SECONDS
------------ ------------- --------------- ---------------
     3981.53      95556.78      5733406.58       344004395

SQL>

Cheers!!

score 0 · Answer 2 · answered Dec 27 '19 at 06:22

As Belayer commented, fractional part nothing but the difference represented in hours,minuets, seconds because SYSDATE is timestamp value by default. If you only want the whole number you may use -

SELECT TRUNC(SYSDATE) - TO_DATE('01-02-2009', 'DD-MM-YYYY')
FROM DUAL;

score 0 · Answer 3 · answered Dec 27 '19 at 10:33

"But doesn't work" is not helpful, what do you expect? The query works exactly as it should work.

Perhaps you are looking for this:

SELECT localtimestamp - TO_DATE('1-02-2009', 'DD-MM-YYYY')
FROM DUAL;

+3981 11:27:50.848929

When you subtract a DATE from a DATE then Oracle returns the number of days between these two dates. But you can subtract from TIMESTAMP, then the result is an INTERVAL DAY TO SECOND, see Datetime/Interval Arithmetic

How can I subtract the system date?

3 Answers3