Sanae BEKKAR – My Blog –

“How do I retrieve the difference between two dates”

The answer is deceptively simple: you just subtract them.

This will return a number representing the number of days between the two dates.

Additionally, you have the built-in function MONTHS_BETWEEN that will return a number representing the number of months—including fractional months—between two dates.

The following SQL query demonstrates the outcome of subtracting two dates (showing the number of days between them), using the MONTHS_BETWEEN function and then the two functions used with INTERVAL types:
EODA@ORA12CR1> select dt2-dt1 ,  

         months_between(dt2,dt1) months_btwn,  

         numtodsinterval(dt2-dt1,’day’) days,  

         numtoyminterval(trunc(months_between(dt2,dt1)),’month’) months  

    from (select to_date(’29-feb-2000 01:02:03′,’dd-mon-yyyy hh24:mi:ss’) dt1,  

               to_date(’15-mar-2001 11:22:33′,’dd-mon-yyyy hh24:mi:ss’) dt2  

           from dual )  

  / 

DT2-DT1       MONTHS_BTWN       DAYS                                                         MONTHS

———- ———– —————————— ————

380.430903            12.5622872          +000000380 10:20:30.000000000  +000000001-00

Most applications would like to display the years, months, days, hours, minutes, and seconds between the dates.

Using a combination of the preceding functions, we can achieve that goal. We’ll select out two intervals: one for the years and months, and the other for just the day, hours, and so on.

We’ll use the MONTHS_BETWEEN built-in function to determine the decimal number of months between the two dates, and then we’ll use the NUMTOYMINTERVAL built-in function to convert that number into the years and months.

Additionally, we’ll use MONTHS_BETWEEN to subtract the integer number of months between the two dates from the larger of the two dates to get down to the days and hours between them:
EODA@ORA12CR1> select numtoyminterval

(trunc(months_between(dt2,dt1)),’month’)

years_months,

numtodsinterval

(dt2-add_months( dt1, trunc(months_between(dt2,dt1)) ),

‘day’ )

days_hours

from (select to_date(’29-feb-2000 01:02:03′,’dd-mon-yyyy hh24:mi:ss’) dt1,

to_date(’15-mar-2001 11:22:33′,’dd-mon-yyyy hh24:mi:ss’) dt2

from dual )

/

YEARS_MONTHS         DAYS_HOURS

————— —————————–

+000000001-00         +000000015 10:20:30.000000000
Now it is clear that there is 1 year, 15 days, 10 hours, 20 minutes, and 30 seconds between the two DATEs.