CODEWITHSUNDEEP
sqloracle-databaseplsql
Harshil Shah
Harshil Shah

Reputation: 21

Difference of date with SYSDATE - Value give something?

When subtracting today's date like below gives correct output.

select 
    (TRUNC(to_date('25/05/2016','dd/mm/yyyy'))-TRUNC(to_date('02/01/2016','dd/mm/yyyy')))
from dual;

output : 144 days

When taking todays date ad sysdate below should give 144. but shows some other value? Why?

select 
    (to_date(SYSDATE,'dd/mm/yyyy'))-(to_date('02/01/2016','dd/mm/yyyy'))
from dual;

output: -730343 (shows some value).

Upvotes: 1

Views: 216

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

When you do:

to_date(SYSDATE,'dd/mm/yyyy')

you're implicitly converting SYSDATE, which is already a date, to a string - using your NLS_DATE_FORMAT. From the result that seems to be DD-MON-RR. So you're really doing:

to_date(to_char(SYSDATE,'DD-MON-RR'),'dd/mm/yyyy')

The inner part gives you the string '25-MAY-16'. When you convert that back to a date with the yyyy mask you have a two-digit year, 16, which is interpreted as year 0016 rather than 2016. You'd actually get what you expect if you used rrrr instead, but that's a happy side effect, and it'll still break in session with different NLS settings:

select to_date(SYSDATE,'dd/mm/yyyy') as bad_nls,
  to_char(to_date(SYSDATE,'dd/mm/yyyy'), 'YYYY-MM-DD') as bad_string,
  to_date(SYSDATE,'dd/mm/rrrr') as ok_nls,
  to_char(to_date(SYSDATE,'dd/mm/rrrr'), 'YYYY-MM-DD') as ok_string
from dual;

BAD_NLS   BAD_STRING OK_NLS    OK_STRING
--------- ---------- --------- ----------
25-MAY-16 0016-05-25 25-MAY-16 2016-05-25

Notice that with your current NLS mask and the implicit conversion to a string you can't tell the difference between the first and third result; but it's obvious that it's wrong when shown with a four-digit year in the second and fourth results.

With your implicit conversion you're comparing 0016-05-25 with 2016-01-02, and it is giving you -730343 as that's how many days there are in 2000 years, adjusted for the 144 days you expected the gap to be.

As Praveen already said you don't need to use to_date() for SYSDATE, and if you're trying to set the time portion to midnight you can just truncate it.

select date '2016-05-25' - date '2016-01-02' as diff1,
  date '2016-05-25' - date '0016-05-25' as diff2,
  date '0016-05-25' - date '2016-01-02' as diff3,
  trunc(sysdate) - date '2016-01-02' as diff4
from dual;

     DIFF1      DIFF2      DIFF3      DIFF4
---------- ---------- ---------- ----------
       144     730487    -730343        144

More generally, though, don't use two-digit years (alas it seems we've already forgotten the lessons of Y2K!), and don't rely on NLS settings.

Upvotes: 5

Praveen
Praveen

Reputation: 9365

You don't need to convert a date [sysdate] to date using to_date
Use trunc to strip the time portion of the sysdate.

Try;

select 
TRUNC(SYSDATE) - (to_date('02/01/2016','dd/mm/yyyy'))
from dual;

Upvotes: 1

Related Questions