In Oracle Database, the MONTHS_BETWEEN() function returns the number of months between two dates.

Syntax

The syntax goes like this:

MONTHS_BETWEEN(date1, date2)

Example

Here’s an example:

SELECT MONTHS_BETWEEN(DATE '2030-06-10', DATE '2030-02-10')
FROM DUAL;

Result:

4

In this case there are exactly four months between the two dates.

The month and the last day of the month are defined by the session parameter NLS_CALENDAR. See How to Check the Values of the NLS Parameters to find out which calendar your session is using. Mine is using the Gregorian calendar.

Fractional Months

The function allows for fractional months. If the two dates include different day portions, then Oracle Database works out the applicable fractional component to use.

Example:

SELECT MONTHS_BETWEEN(DATE '2030-06-10', DATE '2030-02-25')
FROM DUAL;

Result:

3.51612903225806451612903225806451612903

Negative Months

If the second date is later than the first, then a negative amount is returned:

SELECT MONTHS_BETWEEN(DATE '2030-02-10', DATE '2030-06-10')
FROM DUAL;

Result:

-4

Out of Range Dates

Zero dates and other out of range dates result in an error.

Example:

SELECT MONTHS_BETWEEN(DATE '2030-02-10', DATE '0000-00-00')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT MONTHS_BETWEEN(DATE '2030-02-10', DATE '0000-00-00')
FROM DUAL
Error at Command Line : 1 Column : 47
Error report -
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"
*Cause:    Illegal year entered
*Action:   Input year in the specified range

As the error message states, the (full) year must be between -4713 and +9999, and not be 0.

Comparing a Date with the Current Date

We can pass SYSDATE as the datetime argument to compare a date with the current date:

SELECT 
    SYSDATE,
    MONTHS_BETWEEN(SYSDATE, DATE '2030-03-01')
FROM DUAL;

Result:

     SYSDATE     MONTHS_BETWEEN(SYSDATE,DATE'2030-03-01') 
____________ ____________________________________________ 
13/AUG/21       -102.587825194145758661887694145758661888

Missing Argument

Calling MONTHS_BETWEEN() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT MONTHS_BETWEEN()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT MONTHS_BETWEEN()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: