MONTHS function [Date and time]

The MONTHS function manipulates a TIMESTAMP, or returns the number of months between two TIMESTAMP values. See the Remarks section below.

Syntax 1

MONTHS( timestamp-expression )

Syntax 2

MONTHS( timestamp-expression, timestamp-expression )

Syntax 3

MONTHS( timestamp-expression, integer-expression )

Parameters

timestamp-expression A date and time of type TIMESTAMP.
integer-expression The integer number of months (of type SMALLINT) to be added to the timestamp-expression. If integer-expression is negative, the appropriate number of months is subtracted from timestamp-expression. If you supply an integer-expression, the timestamp-expression must be explicitly cast as a TIME, DATE or TIMESTAMP data type. If timestamp-expression is a TIME value, the current month is assumed.
For information about casting data types, see CAST function [Data type conversion].

Returns

Remarks

The result of the MONTHS function depends on its arguments. The MONTHS function ignores hours, minutes, and seconds in its arguments.

Syntax 1 If you pass a single timestamp-expression to the MONTHS function, it will return the number of months between 0000-02 and timestamp-expression as an INTEGER.

Note

0000-02 is not meant to imply an actual date; it is the default date used by the MONTHS function.
Syntax 2 If you pass two TIMESTAMP values to the MONTHS function, the function returns the integer number of months between them.
Syntax 3 If you pass a TIMESTAMP value and a SMALLINT value to the MONTHS function, the function returns the TIMESTAMP result of adding the integer number of months to timestamp-expression.

Instead of Syntax 2, use the DATEDIFF function. Instead of Syntax 3, use the DATEADD function.

The value of MONTHS is calculated from the number of first days of the month between the two dates.

Standards and compatibility

Example

The following statements return the value 2, signifying that the second date is two months after the first. It is recommended that you use the second example (DATEDIFF).

SELECT MONTHS( '1999-07-13 06:07:12', '1999-09-13 10:07:12' );

SELECT DATEDIFF( month,
   '1999-07-13 06:07:12',
   '1999-09-13 10:07:12' );

The following statement returns the value 23981.

SELECT MONTHS( '1998-07-13 06:07:12' );

The following statements return the TIMESTAMP value 1999-10-12 21:05:07.000. It is recommended that you use the second example (DATEADD).

SELECT MONTHS( CAST( '1999-05-12 21:05:07' AS DATETIME ), 5);

SELECT DATEADD( month, 5, '1999-05-12 21:05:07' );

Discuss this page in DocCommentXchange.

MONTHS function [Date and time]

Note