MONTHS_BETWEEN function returns the number of months between 2 dates.
Quick Example:
Get the number of months between February 10, 2012 and January 01, 2012:
SELECT MONTHS_BETWEEN (DATE '2012-02-10', DATE '2012-01-01') FROM dual; -- Result: 1.29032258
Summary information:
Syntax | MONTHS_BETWEEN(date_expression1, date_expression2) |
Negative Result | If date_expression1 is earlier than date_expression2 |
Integer Result | If date_expression1 and date_expression2 have the same day, or both specify the last day of the month |
Decimal Result | If days are different and they are not both specify the last day of the month |
Fractional Part | Always calculated as the difference between days divided by 31 despite the number of days in the month |
Last Update: Oracle 11g Release 2
MONTHS_BETWEEN function returns an integer number if the days are the same:
-- Get the number of months between February 12, 2012 and January 12, 2012 SELECT MONTHS_BETWEEN (DATE '2012-02-12', DATE '2012-01-12') FROM dual; -- Result: 1
Or if the both dates specify the last day of the month:
-- Get the number of months between February 29, 2012 and January 31, 2012 SELECT MONTHS_BETWEEN (DATE '2012-02-29', DATE '2012-01-31') FROM dual; -- Result: 1
In other cases, if the days are different MONTHS_BETWEEN returns a decimal number:
-- Get the number of months between February 29, 2012 and February 01, 2012 SELECT MONTHS_BETWEEN (DATE '2012-02-29', DATE '2012-02-01') FROM dual; -- Result: 0.903225806
Note that MONTHS_BETWEEN always calculates the fractional part as the difference in days divided by 31.
In the example above, the fractional part is calculated as (29 - 1)/31 = 0.903225806, although there are 29 days in February 2012.
You can also see that MONTHS_BETWEEN does not return an integer result if you specify the first and last days of the same month.
Oracle 11g Release 2 SQL Language Reference