Oracle / PLSQL: MONTHS_BETWEEN Function

This Oracle tutorial explains how to use the Oracle/PLSQL MONTHS_BETWEEN function with syntax and examples.

Description

The Oracle/PLSQL MONTHS_BETWEEN function returns the number of months between date1 and date2.

Syntax

The syntax for the MONTHS_BETWEEN function in Oracle/PLSQL is:

MONTHS_BETWEEN( date1, date2 )

Parameters or Arguments

date1

The first date used to calculate the number of months between.

date2

The second date used to calculate the number of months between.

Returns

The MONTHS_BETWEEN function returns a numeric value.

Note

• If a fractional month is calculated, the MONTHS_BETWEEN function calculates the fraction based on a 31-day month.

Applies To

The MONTHS_BETWEEN function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example

Let's look at some Oracle MONTHS_BETWEEN function examples and explore how to use the MONTHS_BETWEEN function in Oracle/PLSQL.

For example:

MONTHS_BETWEEN (TO_DATE ('2003/01/01', 'yyyy/mm/dd'), TO_DATE ('2003/03/14', 'yyyy/mm/dd') )

would return -2.41935483870968

Another example using the MONTHS_BETWEEN function in Oracle/PLSQL is:

MONTHS_BETWEEN (TO_DATE ('2003/07/01', 'yyyy/mm/dd'), TO_DATE ('2003/03/14', 'yyyy/mm/dd') )

would return 3.58064516129032

Another example using the MONTHS_BETWEEN function is:

MONTHS_BETWEEN (TO_DATE ('2003/07/02', 'yyyy/mm/dd'), TO_DATE ('2003/07/02', 'yyyy/mm/dd') )

would return 0

A final example using the MONTHS_BETWEEN function in Oracle/PLSQL is:

MONTHS_BETWEEN (TO_DATE ('2003/08/02', 'yyyy/mm/dd'), TO_DATE ('2003/06/02', 'yyyy/mm/dd') )

would return 2