Oracle MONTHS_BETWEEN() Function

The Oracle MONTHS_BETWEEN() is a built-in function that returns the number of months between two given dates.

Syntax of Oracle MONTHS_BETWEEN()

Here is the syntax for the Oracle MONTHS_BETWEEN() function:

MONTHS_BETWEEN(date1, date2)

Parameters

date1

Required.

date2

Required.

Return Value

The Oracle MONTHS_BETWEEN() function returns the number of months between two given dates.

If date1 is later than date2, the result is positive. If date1 is earlier than date2, the result is negative. If date1 and date2 are the same day of a month or both the last day of a month, the result is always an integer. Otherwise, Oracle database calculates the decimal part of the result based on months with 31 days and takes into account the time component difference between date1 and date2.

If either parameter is NULL, MONTHS_BETWEEN() returns NULL.

Examples of Oracle MONTHS_BETWEEN()

Here are some examples that demonstrate the usage of the Oracle MONTHS_BETWEEN() function.

Basic Usage

To calculate the number of months between two months, use the following statement with MONTHS_BETWEEN():

SELECT
    MONTHS_BETWEEN(DATE '2023-05-20', DATE '2023-02-10') Result
FROM dual;

Output:

                                     RESULT
___________________________________________
   3.32258064516129032258064516129032258065

If you swap the values of the two parameters, you will get a negative result:

SELECT
    MONTHS_BETWEEN(DATE '2023-02-10', DATE '2023-05-20') Result
FROM dual;

Output:

                                      RESULT
____________________________________________
   -3.32258064516129032258064516129032258065

Integer Result

If the days of the two dates are the same or both the last day of a month, MONTHS_BETWEEN() returns an integer.

SELECT
    MONTHS_BETWEEN(DATE '2023-05-20', DATE '2023-02-20') Result1,
    MONTHS_BETWEEN(DATE '2023-05-31', DATE '2023-02-28') Result2
FROM dual;

Output:

   RESULT1    RESULT2
__________ __________
         3          3

NULL Parameters

If either parameter is NULL, MONTHS_BETWEEN() returns NULL.

SET NULL 'NULL';
SELECT
    MONTHS_BETWEEN(NULL, DATE '2023-05-20') NULL_1,
    MONTHS_BETWEEN(DATE '2023-05-20', NULL) NULL_2,
    MONTHS_BETWEEN(NULL, NULL) NULL_3
FROM dual;

Output:

   NULL_1    NULL_2    NULL_3
_________ _________ _________
     NULL      NULL      NULL

In this example, we use the SET NULL 'NULL'; statement to display NULL values as the string 'NULL'.

Conclusion

The Oracle MONTHS_BETWEEN() is a built-in function that returns the number of months between two given dates.