syntax and example of months_between function in oracle database query

Oracle MONTHS_BETWEEN Function is used to return the months between two dates. SQL MONTHS_BETWEEN Function can be used with String parameters.

Oracle MONTHS_BETWEEN Function Syntax

SELECT MONTHS_BETWEEN(date1, date2)
FROM table_name;

Here in the Oracle MONTHS_BETWEEN Function Syntax

Date1 and date2 are the two dates passed to calculate months between these two dates.
If the difference between the months is in fraction, then the MONTHS_BETWEEN function calculates the fractional difference based on 31 day a month logic.
If date1 is ahead of date2 then a positive difference is returned else a negative difference will be returned.

SQL MONTHS_BETWEEN Function can be used with Strings as parameters.

For example, the SQL MONTHS_BETWEEN Function query returns the months between the dates passed as string.

Note that the date1 is before date2.

SELECT MONTHS_BETWEEN('01-JAN-2012','01-MAR-2012')
FROM DUAL;

The above SQL MONTHS_BETWEEN Function query returns “-2” as date1 is before date2 and difference between months is 2.

SQL MONTHS_BETWEEN Function query below returns the months between the dates passed as string.

Note that date2 is before date1.

SELECT MONTHS_BETWEEN('01-MAR-2012','01-JAN-2012')
FROM DUAL;

Above SQL MONTHS_BETWEEN Function query returns “2” as date1 is ahead of date2 and difference between months is 2.

In the below SQL MONTHS_BETWEEN Function query we have passed same date as both the parameters.

SELECT MONTHS_BETWEEN('01-MAR-2012','01-MAR-2012')
FROM DUAL;

Above SQL MONTHS_BETWEEN Function query “0” because date1 is equal to date2 and difference between months becomes 0.

SQL MONTHS_BETWEEN Function returns decimal values also if the difference between the dates is not a whole number.

For example, SQL MONTHS_BETWEEN Function query below returns months between dates which are more than 1 month and less than 2 months apart.

Note date1 is ahead of date2.

SELECT MONTHS_BETWEEN('30-APR-2012','01-MAR-2012')
FROM DUAL;

Will return “1.93548387096774” as the difference between two dates.

SQL MONTHS_BETWEEN Function query below returns months between dates which are more than 1 month and less than 2 months apart.

Note date1 is before date2.

SELECT MONTHS_BETWEEN('01-MAR-2012','30-APR-2012')
FROM DUAL;

Will return “-1.93548387096774” as the difference between two dates.