How to Accurately Calculate the Number of Days Between Two Dates in Oracle 11g?

Determining the Number of Days Between Dates in Oracle 11g

In Oracle 11g, calculating the number of days between two dates requires a straightforward approach. Despite attempting to convert an interval to an integer, the results are not as expected.

Fortunately, an alternative solution exists that yields the desired integer value. By using the trunc function to round down the start date to the nearest whole day, you can subtract it from the end date:

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

This approach returns a NUMBER data type representing the integer number of days between the specified dates. For example:

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)

The above is the detailed content of How to Accurately Calculate the Number of Days Between Two Dates in Oracle 11g?. For more information, please follow other related articles on the PHP Chinese website!