The MONTHS_BETWEEN function returns an estimate of the
number of months between expression1 and expression2.

>>-MONTHS_BETWEEN--(--expression1--,--expression2--)-----------><
- expression1
- An expression that returns a value of one of the following built-in
data types: a date, a timestamp, a character-string, or a graphic-string.
- If expression1 is a character or graphic
string, it must not be a CLOB or DBCLOB and its values must be a valid
string representation of a date or timestamp. For the valid formats
of string representations of dates and timestamps, see String representations of datetime values
- expression2
- An expression that returns a value of one of the following built-in
data types: a date, a timestamp, a character-string, or a graphic-string.
- If expression2 is a character or graphic
string, it must not be a CLOB or DBCLOB and its values must be a valid
string representation of a date or timestamp. For the valid formats
of string representations of dates and timestamps, see String representations of datetime values
If
expression1 represents a
date that is later than
expression2, then
the result is positive. If
expression2 represents
a date that is later than
expression1, then
the result is negative.

- If expression1 and expression2 represent
dates with the same day of the month or the last 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). If either
argument can be null, the result can be null; if either argument is
null, the result is the null value.
Examples
- Calculate the months between two dates:
SELECT MONTHS_BETWEEN('2005-01-17', '2005-02-17')
FROM SYSIBM.SYSDUMMY1
Returns
the value -1.000000000000000 SELECT MONTHS_BETWEEN('2005-02-20', '2005-01-17')
FROM SYSIBM.SYSDUMMY1
Returns
the value 1.096774193548387
The following table contains additional examples:Table 1. Additional examples using MONTHS_BETWEEN| Value for argument e1 |
Value for argument e2 |
Value returned by MONTHS_BETWEEN (e1,e2) |
Value returned by
ROUND(
MONTHS_BETWEEN(
e1,e2)*31,2)
|
Comment |
| 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.000000000000000 |
31.00 |
See Note 1 |
| 2007–03–15 |
2007–02–20 |
0.838709677419354 |
26.00 |
See Note 2 |
| 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.000000000000000 |
31.00 |
See Note 3 |
Note:- The time difference is ignored because the day of the month is
the same for both values.
- The result is not 23 because, even though February has 28 days,
the assumption is that all months have 31 days.
- 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.
