Oracle - MONTHS_BETWEEN - Get Number of Months Between Two Dates

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:

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.

• Reference and Articles
• Functions

Oracle 11g Release 2 SQL Language Reference

• MONTHS_BETWEEN Function