Oracle Date/Time Function - Oracle/PLSQL MONTHS_BETWEEN Function






This Oracle tutorial explains how to use the Oracle/PLSQL MONTHS_BETWEEN function.

The Oracle/PLSQL MONTHS_BETWEEN function returns the number of months between date1 and date2.

MONTHS_BETWEEN(x,y) returns the number of months between x and y. If x is before y on the calendar, the number returned is positive; otherwise the number is negative.

Syntax

The syntax for the Oracle/PLSQL MONTHS_BETWEEN function is:

MONTHS_BETWEEN( date1, date2 )

date1 and date2 are the dates used to calculate the number of months.

If a fractional month is calculated, the MONTHS_BETWEEN function calculates the fraction based on a 31-day month.





Example


SQL> SELECT MONTHS_BETWEEN('25-MAY-2009', '15-JAN-2012') FROM dual;

MONTHS_BETWEEN('25-MAY-2009','15-JAN-2012')
-------------------------------------------
                                 -31.677419

SQL>

The next example reverses the same dates and the returned result is a negative number of months:


SQL> SELECT MONTHS_BETWEEN('15-JAN-2012', '25-MAY-2009')FROM dual;

MONTHS_BETWEEN('15-JAN-2012','25-MAY-2009')
-------------------------------------------
                                 31.6774194

SQL>