MONTHS function [Date and time]

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 )

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

The first syntax returns the number of months since an arbitrary starting date. This number is often useful for determining whether two date/time expressions are in the same month in the same year.

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.