Oracle SQL: the Essential Reference

MONTHS_BETWEEN

MONTHS_BETWEEN(d1,d2)

Returns the number of months between the two dates d1 and d2.

If both dates represent the same day of the month or represent the last day of their respective months, an integer value will be returned. Otherwise, MONTHS_BETWEEN will return a fractional value.

Examples

SQL> SELECT MONTHS_BETWEEN(TO_DATE('29-Dec-1999'), TO_DATE('29-Dec-1988'))
  2  FROM dual;

MONTHS_BETWEEN(TO_DATE('29-DEC-1999'),TO_DATE('29-DEC-1988'))
-------------------------------------------------------------
                                                          132

SQL> SELECT MONTHS_BETWEEN(TO_DATE('12-May-2000'), TO_DATE('29-Dec-1988'))
  2  FROM dual;

MONTHS_BETWEEN(TO_DATE('12-MAY-2000'),TO_DATE('29-DEC-1988'))
-------------------------------------------------------------
                                                   136.451613

The fractional value returned by MONTHS_BETWEEN is based on a 31-day month. This value gets a bit weird when you cross a month boundary. In the second example, you can see that 136 months gets you from 29-Dec-1988 to 29-Apr-2000. Multiply the fractional portion of 0.451613 by 31, and you get 14 days. This seems rather strange since there are only 13 days between April 29 and May 12, but for purposes of calculating the fractional result, the MONTHS_BETWEEN function uses 31 for the number of days in April.