Sql – How to get the number of days between 2 dates in Oracle 11g

oracle, oracle11g, sql

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.

Best Solution

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----------        29SQL> desc v Name                   Null?    Type ---------------------- -------- ------------------------ DIFF                            NUMBER(38)