Returns the number of months since an arbitrary starting date/time or the number of months between two specified date/times, or adds the specified integer-expression number of months to a date/time.
MONTHS ( date-expression
| date-expression, datetime-expression
| date-expression, integer-expression )
Parameter |
Description |
|---|---|
date-expression |
A date and time. |
integer-expression |
The number of months to be added to the date-expression. If integer-expression is negative, the appropriate number of months are subtracted from the date/time value. If you supply an integer expression, the date-expression must be explicitly cast as a datetime data type. |
INT
TIMESTAMP
The following statement returns the value 23982:
SELECT MONTHS( '1998-07-13 06:07:12' ) FROM iq_dummy
The following statement returns the value 2, to signify the difference between the two dates:
SELECT MONTHS( '1999-07-13 06:07:12', '1999-09-13 10:07:12' ) FROM iq_dummy
The following statement returns the datetime value 1999-10-12 21:05:07.000:
SELECT MONTHS( CAST( '1999-05-12 21:05:07' AS DATETIME ), 5) FROM iq_dummy
MONTHS( invoice_sent ) = MONTHS( payment_received )
Comparing the MONTH function would incorrectly include a payment made 12 months after the invoice was sent.
The second syntax returns the number of months from the first date to the second date. The number might be negative. It is calculated from the number of the first days of the month between the two dates. Hours, minutes and seconds are ignored.
The third syntax adds integer-expression months to the given date. If the new date is past the end of the month (such as MONTHS ('1992-01-31', 1) ) the result is set to the last day of the month. If integer-expression is negative, the appropriate number of months are subtracted from the date. Hours, minutes and seconds are ignored.