MONTHS_BETWEEN scalar function

The MONTHS_BETWEEN function returns an estimate of the number of months between two arguments.

Read syntax diagramSkip visual syntax diagramMONTHS_BETWEEN( expression1, expression2)

The schema is SYSIBM.

expression1 or expression2
Expressions that return a value of any of the following built-in data types: a date, a timestamp, a character string, or a graphic string. If either expression is a character or graphic string, it must not be a CLOB or DBCLOB, and its value must be a valid string representation of a date or timestamp with an actual length that is not greater than 255 bytes. A time zone in a string representation of a timestamp is ignored. For the valid formats of string representations of dates and timestamps, see String representations of datetime values.

If expression1 is a TIMESTAMP WITH TIME ZONE value, expression1 is first cast to TIMESTAMP WITHOUT TIME ZONE with the same precision as expression1. If expression2 is a TIMESTAMP WITH TIME ZONE value, expression2 is first cast to TIMESTAMP WITHOUT TIME ZONE with the same precision as expression2.

If expression1 represents a date that is later than expression2, the result is positive. If expression1 represents a date that is earlier than expression2, the result is negative.

  • If expression1 and expression2 represent dates or timestamps with the same day of the month, or both arguments represent the last day of their respective months, the result is a the whole number difference based on the year and month values, ignoring any time portions of timestamp arguments.
  • Otherwise, the whole number part of the result is the difference based on the year and month values. The fractional part of the result is calculated from the remainder based on an assumption that every month has 31 days. If either argument represents a timestamp, the arguments are effectively processed as timestamps with maximum precision, and the time portions of these values are also considered when determining the result.

The result of the function is a DECIMAL(31,15).

The result can be null; if any argument is null, the result is the null value.

Examples 1: The following example calculates the months between two dates: 
   SELECT MONTHS_BETWEEN ('2008-01-17','2008-02-17') 
       AS MONTHS_BETWEEN
       FROM SYSIBM.SYSDUMMY1;
The results of this statement are similar to the following results: 
MONTHS_BETWEEN
----------------------------

-1.00000000000000
Examples 2: The following example calculates the months between two dates: 
   SELECT MONTHS_BETWEEN ('2008-02-20','2008-01-17') 
       AS MONTHS_BETWEEN 
       FROM SYSIBM.SYSDUMMY1;
The results of this statement are similar to the following results: 
MONTHS_BETWEEN
----------------------------

1.096774193548387
Example 3: Calculate the number of months that project AD3100 will take. Assume that the start date is 1982-01-01 and the end date is 1983-02-01:
SELECT MONTHS_BETWEEN (PRENDATE, PRSDATE)  
       FROM PROJECT
       WHERE PROJNO='AD3100';
The result is 13.000000000000000.

Example 4: The following table illustrates the use of the MONTHS_BETWEEN function in certain situations:

Table 1. Additional examples using MONTHS_BETWEEN
Value for expression1 Value for expression2 Value returned by MONTHS_BETWEEN (expression1,expression2) Value returned by ROUND ( MONTHS_BETWEEN (expression1,expression2)*31,2 )
2005-02-02 2005-01-01 1.032258064516129 32.00
2007-11-01-09.00.00.00000 2007-12-07-14.30.12.12345 -1.200945386592741 -37.23
2007-12-13-09.40.30.00000 2007-11-13-08.40.30.00000 1.0000000000000001 31.001
2007–03–15 2007–02–20 0.8387096774193542 26.002
2008-02-29 2008-02-28-12.00.00 0.016129032258064 0.50
2008-03-29 2008-02-29 1.000000000000000 31.00
2008-03-30 2008-02-29 1.032258064516129 32.00
2008-03-31 2008-02-29 1.0000000000000003 31.003
Notes:
  1. The time difference is ignored because the day of the month is the same for both values.
  2. The result is not 23 because, even though February has 28 days, the assumption is that all months have 31 days.
  3. The result is not 33 because both dates are the last day of their respective month, and so the result is only based on the year and month portions.