The MONTHS_BETWEEN function returns the difference between the given DATE value. The return value is DOUBLE type. An integer value is returned if the two dates specified as arguments are identical or are the last day of the given month; otherwise, a value obtained by dividing the day difference by 31 is returned.
MONTHS_BETWEEN(date_argument, date_argument)
date_argument :
• date
• NULL
--it returns the negative months when the first argument is the previous date
SELECT MONTHS_BETWEEN(DATE '2008-12-31', DATE '2010-6-30');
months_between(date '2008-12-31', date '2010-6-30')
======================================================
-1.800000000000000e+001
--it returns integer values when each date is the last dat of the month
SELECT MONTHS_BETWEEN(DATE '2010-6-30', DATE '2008-12-31');
months_between(date '2010-6-30', date '2008-12-31')
======================================================
1.800000000000000e+001
--it returns months between two arguments when explicitly casted to DATE type
SELECT MONTHS_BETWEEN(CAST (SYS_TIMESTAMP AS DATE), DATE '2008-12-25');
months_between( cast( SYS_TIMESTAMP as date), date '2008-12-25')
====================================================================
1.332258064516129e+001
--it returns months between two arguments when explicitly casted to DATE type
SELECT MONTHS_BETWEEN(CAST (SYS_DATETIME AS DATE), DATE '2008-12-25');
months_between( cast( SYS_DATETIME as date), date '2008-12-25')
===================================================================
1.332258064516129e+001