How can I get the number of days between 2 dates in Oracle 11g?

Question

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.

Answer 1

I figured it out myself. I need

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

Answer 2

you can do :

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

Answer 3

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)