MONTHS_BETWEEN Function

The MONTHS_BETWEEN function accepts two DATE or DATETIME expressions as arguments, and returns a signed DECIMAL value that quantifies the interval between those arguments in months, as if month were a unit of time.

This function requires two arguments, each of which can be a DATE expression or a DATETIME expression.

The value returned is a DECIMAL data type, representing the difference between the two arguments, expressed as a DECIMAL value based on 31-day units. If the first argument is a point in time later than the second argument, the sign of the returned value is positive. If the first argument is earlier than the second argument, the sign of the returned value is negative. If both arguments are equal, the return value is zero.

If the dates of the arguments are both the same days of a month or are both the last days of a months, the result is a whole number. Otherwise, the fractional portion of the result is calculated, based on a month of 31 days . This fractional part can also include the difference in hour, minute, and second time units, unless both arguments are DATE expressions.

The following query calls the MONTHS_BETWEEN function in the Projection clause, using two DATE values returned by TO_DATE expressions as arguments.

SELECT MONTHS_BETWEEN(TO_DATE('2-2-2005', '%m-%d-%Y'),
                      TO_DATE('1-1-2005',  '%m-%d-%Y')) 
AS lunations FROM systables WHERE tabid = 1;

The value returned by the query expresses the 32-day difference between the two DATE arguments as a positive number of 31-day months:

          months
1.03225806451613

The next example returns the DATETIME column expression arguments to MONTHS_BETWEEN expressions, and their differences in months for two rows of a table:

SELECT d_datetime, e_datetime, 
   MONTHS_BETWEEN(d_datetime, e_datetime) AS months_between
   FROM mytab1;


d_datetime      2007-11-01 09:00:00.00000 
e_datetime      2007-12-07 14:30:12.12345 
months_between  -1.2009453405018


d_datetime      2007-12-13 09:40:30.00000 
e_datetime      2007-11-13 08:40:30.00000 
months_between  1.00000000000000

Here the first MONTHS_BETWEEN result includes differences in time units smaller than days. The second result has no fractional part, because the day time units of the arguments had the same value.

The MONTHS_BETWEEN expressions in the next example compares DATE and DATETIME values:

SELECT col_datetime, col_date,
   MONTHS_BETWEEN(col_datetime, col_date) AS months_between
   FROM mytab2;


col_datetime    2008-12-13 08:40:30.00000 
col_date        11/13/2007 
months_between  13.0000000000000

Because both arguments specify the same day of the month, the result has no fractional part.