ASA SQL Reference
SQL Functions
Alphabetical list of functions
Given two dates, this function returns the integer number of months between them. It is recommended that you use the DATEDIFF function [Date and time] instead for this purpose.
Given a single date, this function returns the number of months since 0000-02.
Given one date and an integer, it adds the integer number of months to the specified date. It is recommended that you use the DATEADD function [Date and time] instead for this purpose.
Syntax 1 returns an integer. Syntax 2 returns a timestamp.
MONTHS ( [ datetime-expression, ] datetime-expression )
MONTHS ( datetime-expression, integer-expression )
datetime-expression A date and time.
integer-expression The number of months to be added to the datetime-expression. If integer-expression is negative, the appropriate number of months is subtracted from the datetime value. If you supply an integer expression, the datetime-expression must be explicitly cast as a datetime data type.
For information on casting data types, see CAST function [Data type conversion].
The value of MONTHS is calculated from the number of first days of the month between the two dates.
SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase Not supported by Adaptive Server Enterprise.
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 23 982.
SELECT MONTHS( '1998-07-13 06:07:12' )
The following statements return the timestamp 1999-10-12 21:05:07.0. 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' )