Oracle Database Dates Date Arithmetic - Difference between Dates in Months or Years
Example
The difference in months between two dates can be found using the MONTHS_BETWEEN( date1, date2 ):
SELECT MONTHS_BETWEEN( DATE '2016-03-10', DATE '2015-03-10' ) AS difference FROM DUAL;
Outputs:
DIFFERENCE
----------
12
If the difference includes part months then it will return the fraction of the month based on there being 31 days in each month:
SELECT MONTHS_BETWEEN( DATE '2015-02-15', DATE '2015-01-01' ) AS difference FROM DUAL;
Outputs:
DIFFERENCE
----------
1.4516129
Due to MONTHS_BETWEEN assuming 31 days per month when there can be fewer days per month then this can result in different values for differences spanning the boundaries between months.
Example:
SELECT MONTHS_BETWEEN( DATE'2016-02-01', DATE'2016-02-01' - INTERVAL '1' DAY ) AS "JAN-FEB",
MONTHS_BETWEEN( DATE'2016-03-01', DATE'2016-03-01' - INTERVAL '1' DAY ) AS "FEB-MAR",
MONTHS_BETWEEN( DATE'2016-04-01', DATE'2016-04-01' - INTERVAL '1' DAY ) AS "MAR-APR",
MONTHS_BETWEEN( DATE'2016-05-01', DATE'2016-05-01' - INTERVAL '1' DAY ) AS "APR-MAY"
FROM DUAL;
Output:
JAN-FEB FEB-MAR MAR-APR APR-MAY
------- ------- ------- -------
0.03226 0.09677 0.03226 0.06452
The difference in years can be found by dividing the month difference by 12.
