48

I'm trying to find an integer number of days between two dates in Oracle 11g.

I can get close by doing 

select sysdate - to_date('2009-10-01', 'yyyy-mm-dd') from dual

but this returns an interval, and I haven't been successful casting this to an integer.

Edit: Apparently in 10g, this returns the number of days as an integer.

Thorsten
  • 12,921
  • 17
  • 60
  • 79
Brian Ramsay
  • 7,536
  • 8
  • 41
  • 52
  • 5
    that SQL should return a numeric value, not an interval – skaffman Oct 29 '09 at 19:29
  • 2
    Only 10+ years later - [here's a db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=22fb6f998fa99e722472e5bbb7b3bfb5) which demonstrates the difference between subtracting a date from a date (e.g. SYSDATE) and subtracting a date from a timestamp (e.g. SYSTIMESTAMP). The former produces a number representing the count of days between the two dates, while the latter produces an interval representation of the days between the two dates. Actually, introducing a TIMESTAMP anywhere in the calculation results in an INTERVAL being returned, so perhaps OP had redefined SYSDATE. – Bob Jarvis - Слава Україні Dec 27 '19 at 12:46

6 Answers6

58

Or you could have done this:

select trunc(sysdate) - to_date('2009-10-01', 'yyyy-mm-dd') from dual

This returns a NUMBER of whole days:

SQL> create view v as 
  2  select trunc(sysdate) - to_date('2009-10-01', 'yyyy-mm-dd') diff 
  3  from dual;

View created.

SQL> select * from v;

      DIFF
----------
        29

SQL> desc v
 Name                   Null?    Type
 ---------------------- -------- ------------------------
 DIFF                            NUMBER(38)
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 3
    This still returns an INTERVAL DAY TO SECOND – Brian Ramsay Oct 29 '09 at 19:36
  • 1
    No it doesn't, what makes you say that? – Tony Andrews Oct 30 '09 at 10:16
  • 1
    Proof that it returns a NUMBER and not an INTERVAL DAY TO SECOND added to answer. – Tony Andrews Oct 30 '09 at 10:20
  • Not relevant now, but for completeness' sake. 10g was returning a NUMBER, and 11g was returning an INTERVAL DAY TO SECOND. – Brian Ramsay Mar 22 '23 at 14:18
  • @BrianRamsay It's odd that you had that experience, but I can assure you that 11g returned a NUMBER also - as does 19c which I use today. If the behaviour had changed beween 10g and 11g, an awful lot of code would have been broken and Oracle would have had to issue a fix PDQ! You can confirm 11g behaviour on sqlfiddle.com – Tony Andrews Mar 24 '23 at 09:04
27

I figured it out myself. I need 

select extract(day from sysdate - to_date('2009-10-01', 'yyyy-mm-dd')) from dual
Brian Ramsay
  • 7,536
  • 8
  • 41
  • 52
  • 8
    Weird. As with the other answerers, when I do your original query, I get a number, not an interval. And when I try your solution, I get an error, ORA-30076: invalid extract field for extract source. This is in Oracle 10g; what version are you using? – Dave Costa Oct 29 '09 at 19:59
  • 11g. That could be it. Thanks Dave. – Brian Ramsay Oct 29 '09 at 20:03
  • Using systimestamp instead of sysdate means that it does timestamp rather than date arithmetic, which gives INTERVALs as a result. But I can't reproduce your experience with sysdate. – Gary Myers Nov 02 '09 at 21:41
  • 2
    select extract(day from sysdate - cast(date '2009-10-01' as timestamp)) from dual – jurijcz Feb 12 '15 at 15:06
  • 2
    I think @Tony Andrews answers the question better. – Delali Feb 28 '19 at 18:30
6

You can try using:

select trunc(sysdate - to_date('2009-10-01', 'yyyy-mm-dd')) as days from dual
SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
Sabeen Malik
  • 10,816
  • 4
  • 33
  • 50
1

This will work i have tested myself.
It gives difference between sysdate and date fetched from column admitdate

  TABLE SCHEMA:
    CREATE TABLE "ADMIN"."DUESTESTING" 
    (   
  "TOTAL" NUMBER(*,0), 
"DUES" NUMBER(*,0), 
"ADMITDATE" TIMESTAMP (6), 
"DISCHARGEDATE" TIMESTAMP (6)
    )

EXAMPLE:
select TO_NUMBER(trunc(sysdate) - to_date(to_char(admitdate, 'yyyy-mm-dd'),'yyyy-mm-dd')) from admin.duestesting where total=300
Akki
  • 1,221
  • 3
  • 14
  • 33
1

  • Full days between end of month and start of today, including the last day of the month:

    SELECT LAST_DAY (TRUNC(SysDate)) - TRUNC(SysDate) + 1 FROM dual

  • Days between using exact time:

    SELECT SysDate - TO_DATE('2018-01-01','YYYY-MM-DD') FROM dual
Stephen Rauch
  • 47,830
  • 31
  • 106
  • 135
Oleksiy
  • 11
  • 1
0

Please refer to the below query for your answer. I am choosing a dummy date in the second part. You can replace it with any date per your requirements.

SELECT TO_DATE(SysDate,'YYYY-MM-DD')  - TO_DATE('2018-01-01','YYYY-MM-DD') FROM dual
Anchit
  • 141
  • 1
  • 5