Retrieving the Number of Days Between Dates in Oracle 11g

Question:

How can I obtain the exact number of days between two dates in Oracle 11g? Using the expression sysdate - to_date('2009-10-01', 'yyyy-mm-dd') yields an interval rather than an integer.

Answer:

To extract the number of days as an integer in Oracle 11g, you can use either of the following methods:

Method 1:

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

The trunc() function removes the time component from the current date, leaving only the date portion.

Method 2:

create view v as 
select sysdate - to_date('2009-10-01', 'yyyy-mm-dd') from dual;

select * from v;

This method creates a view named v that stores the number of days between the two dates as a NUMBER(38) data type.