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

select   dt1, dt2,  trunc( months_between(dt2,dt1) ) mths,   dt2 - add_months( dt1, trunc(months_between(dt2,dt1)) ) daysfrom(    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

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).

I think that your question is not defined well enough, for the following reason.

Answers relying on months_between have to deal with the following issue: that the function reports exactly one month between 2013-02-28 and 2013-03-31, and between 2013-01-28 and 2013-02-28, and between 2013-01-31 and 2013-02-28 (I suspect that some answerers have not used these functions in practice, or are now going to have to review some production code!)

This is documented behaviour, in which dates that are both the last in their respective months or which fall on the same day of the month are judged to be an integer number of months apart.

So, you get the same result of "1" when comparing 2013-02-28 with 2013-01-28 or with 2013-01-31, but comparing it with 2013-01-29 or 2013-01-30 gives 0.967741935484 and 0.935483870968 respectively -- so as one date approaches the other the difference reported by this function can increase.

If this is not an acceptable situation then you'll have to write a more complex function, or just rely on a calculation that assumes 30 (for example) days per month. In the latter case, how will you deal with 2013-02-28 and 2013-03-31?