MONTHS_BETWEEN

The MONTHS_BETWEEN function returns number of months between dates date1 and date2.

SQL syntax

MONTHS_BETWEEN(date1, date2)

Parameters

MONTHS_BETWEEN has the parameters:

Parameter Description

date1

A datetime value or any value that can be converted to a datetime value.

date2

A datetime value or any value that can be converted to a datetime value.

Description

Input parameters can be any combination of all supported datetime data types, excluding the TIME or TT_TIME data types. The supported datetime data types include DATE, TIMESTAMP, TT_DATE, TT_TIMESTAMP, ORA_DATE, and ORA_TIMESTAMP. See "Data Types" for details on datetime data types.

The return data type is a NUMBER.

MONTHS_BETWEEN returns number of months between dates date1 and date2.

  • If date1 is later than date2, the returned result is positive.

  • If date1 is earlier than date2, the returned result is negative.

  • If date1 and date2 are both either the same day of the month or the last day of the month, the returned result is an integer. For all other cases, the returned result is a fraction based on a 31-day month that considers the difference in time components for date1 and date2 parameters.

Examples

The following examples calculate months between two given dates.

Command> SELECT MONTHS_BETWEEN(DATE '1995-02-02', DATE '1995-01-01') 
           AS Months FROM dual;
 
MONTHS
< 1.03225806451613 >
1 row found. 

Command> SELECT MONTHS_BETWEEN(DATE '2010-02-02', DATE '2010-10-01') "Months"
         FROM dual;
 
MONTHS
< -7.96774193548387 >
1 row found.

The following command uses CAST to explicitly convert CHAR strings into timestamps. The first result is rounded to an integer. 

Command> SELECT ROUND ( MONTHS_BETWEEN (CAST ('2010-04-15 14:13:52' 
           AS TIMESTAMP), CAST ('2000-12-31 00:00:00' AS TIMESTAMP))), 
           MONTHS_BETWEEN (CAST ('2010-04-15 14:13:52' AS TIMESTAMP), 
           CAST ('2000-12-31 00:00:00' AS TIMESTAMP)) 
         FROM dual;

< 112, 111.502998805257 >
1 row found.