Get the difference between two dates both In Months and days in sql

Question

I need to get the difference between two dates say if the difference is 84 days, I should probably have output as 2 months and 14 days, the code I have just gives the totals. Here is the code

SELECT Months_between(To_date('20120325', 'YYYYMMDD'),
       To_date('20120101', 'YYYYMMDD'))
       num_months,
       ( To_date('20120325', 'YYYYMMDD') - To_date('20120101', 'YYYYMMDD') )
       diff_in_days
FROM   dual; 

Output is:

NUM_MONTHS    DIFF_IN_DAYS
2.774193548       84

I need for example the output for this query to be either 2 months and 14 days at worst, otherwise I won't mind if I can have the exact days after the months figure because those days are not really 14 because all months do not have 30 days.

Answer 1

select 
  dt1, dt2,
  trunc( months_between(dt2,dt1) ) mths, 
  dt2 - add_months( dt1, trunc(months_between(dt2,dt1)) ) days
from
(
    select date '2012-01-01' dt1, date '2012-03-25' dt2 from dual union all
    select date '2012-01-01' dt1, date '2013-01-01' dt2 from dual union all
    select date '2012-01-01' dt1, date '2012-01-01' dt2 from dual union all
    select date '2012-02-28' dt1, date '2012-03-01' dt2 from dual union all
    select date '2013-02-28' dt1, date '2013-03-01' dt2 from dual union all
    select date '2013-02-28' dt1, date '2013-04-01' dt2 from dual union all
    select trunc(sysdate-1)  dt1, sysdate               from dual
) sample_data

Results:

|                        DT1 |                       DT2 | MTHS |     DAYS |
----------------------------------------------------------------------------
|  January, 01 2012 00:00:00 |   March, 25 2012 00:00:00 |    2 |       24 |
|  January, 01 2012 00:00:00 | January, 01 2013 00:00:00 |   12 |        0 |
|  January, 01 2012 00:00:00 | January, 01 2012 00:00:00 |    0 |        0 |
| February, 28 2012 00:00:00 |   March, 01 2012 00:00:00 |    0 |        2 |
| February, 28 2013 00:00:00 |   March, 01 2013 00:00:00 |    0 |        1 |
| February, 28 2013 00:00:00 |   April, 01 2013 00:00:00 |    1 |        1 |
|   August, 14 2013 00:00:00 |  August, 15 2013 05:47:26 |    0 | 1.241273 |

Link to test: SQLFiddle

Answer 2

Updated for correctness. Originally answered by @jen.

with DATES as (
   select TO_DATE('20120101', 'YYYYMMDD') as Date1,
          TO_DATE('20120325', 'YYYYMMDD') as Date2
   from DUAL union all
   select TO_DATE('20120101', 'YYYYMMDD') as Date1,
          TO_DATE('20130101', 'YYYYMMDD') as Date2
   from DUAL union all
   select TO_DATE('20120101', 'YYYYMMDD') as Date1,
          TO_DATE('20120101', 'YYYYMMDD') as Date2
   from DUAL union all
   select TO_DATE('20130228', 'YYYYMMDD') as Date1,
          TO_DATE('20130301', 'YYYYMMDD') as Date2
   from DUAL union all
   select TO_DATE('20130228', 'YYYYMMDD') as Date1,
          TO_DATE('20130401', 'YYYYMMDD') as Date2
   from DUAL
), MONTHS_BTW as (
   select Date1, Date2,
          MONTHS_BETWEEN(Date2, Date1) as NumOfMonths
   from DATES
)
select TO_CHAR(Date1, 'MON DD YYYY') as Date_1,
       TO_CHAR(Date2, 'MON DD YYYY') as Date_2,
       NumOfMonths as Num_Of_Months,
       TRUNC(NumOfMonths) as "Month(s)",
       ADD_MONTHS(Date2, - TRUNC(NumOfMonths)) - Date1 as "Day(s)"
from MONTHS_BTW;

SQLFiddle Demo :

    +--------------+--------------+-----------------+-----------+--------+
    |   DATE_1     |   DATE_2     | NUM_OF_MONTHS   | MONTH(S)  | DAY(S) |
    +--------------+--------------+-----------------+-----------+--------+
    | JAN 01 2012  | MAR 25 2012  | 2.774193548387  |        2  |     24 |
    | JAN 01 2012  | JAN 01 2013  | 12              |       12  |      0 |
    | JAN 01 2012  | JAN 01 2012  | 0               |        0  |      0 |
    | FEB 28 2013  | MAR 01 2013  | 0.129032258065  |        0  |      1 |
    | FEB 28 2013  | APR 01 2013  | 1.129032258065  |        1  |      1 |
    +--------------+--------------+-----------------+-----------+--------+

Notice, how for the last two dates, Oracle reports the decimal part of months (which gives days) incorrectly. 0.1290 corresponds to exactly 4 days with Oracle considering 31 days in a month (for both March and April).