>>-MONTHS_BETWEEN--(--expression1--,--expression2--)-----------><
The schema is SYSIBM.
The MONTHS_BETWEEN function
returns an estimate of the number of months between expression1 and expression2.
- expression1 or expression2
- Expressions that return a value of either a DATE
or TIMESTAMP data type.
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). 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 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.
Here are some additional examples to consider:
Table 1. Additional examples using MONTHS_BETWEENValue 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.