MONTHS_BETWEEN (date1, date2)
Used to calculate how many months there are between date1 and date2. If date1 is later than date2 in the calendar, then MONTHS_BETWEEN() returns a positive number.
If date1 is earlier than date2 in the calendar, then MONTHS_BETWEEN() returns a negative number.
If date1 and date2 are the same, then MONTHS_BETWEEN() returns a 0.
The experiment is as follows:
SQL> select months_between(to_date('2014-3-21','yyyy-mm-dd'), to_date('2014-1-10','yyyy-mm-dd')) months
2 from dual;
MONTHS
----------
2.35483871
SQL> select months_between(to_date('2014-1-10','yyyy-mm-dd'), to_date('2014-3-21','yyyy-mm-dd')) months
2 from dual;
MONTHS
----------
-2.3548387
SQL> select months_between(to_date('2014-1-10','yyyy-mm-dd'), to_date('2014-1-10','yyyy-mm-dd')) months
2 from dual;
MONTHS
----------
0
-- Between 2014.3.21 and 2014.1.10, the difference is 2 months plus 11 days, and the 11 days are converted into decimals by month (in oracle, 31 days are the base):
SQL> select 11/31 from dual;
11/31
----------
.35483871
For details, please refer to the 11.2 online documentation:
/cd/E11882_01/server.112/e41084/#SQLRF00669
MONTHS_BETWEEN returns number of months between dates date1 and date2. The month and the last day of the month are defined by the parameter NLS_CALENDAR. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer.Otherwise Oracle Database calculates the fractional portion of the result based on a31-day month and considers the difference in time components date1 and date2.