MONTHS function [Date and time]

The information you specify changes the behavior of this function:

  • If you give a single date, this function returns the number of months since 0000-02.

    Note

    0000-02 is not meant to imply an actual date; it is the date used by the date algorithm.

  • If you give two time stamps, this function returns the integer number of months between them. Instead, use the DATEDIFF function.

  • If you give a date and an integer, this function adds the integer number of minutes to the specified timestamp. Instead, use the DATEADD function.

Syntax 1: integer 
MONTHS( [ datetime-expression, ] datetime-expression )
Syntax 2: timestamp 
MONTHS( datetime-expression, integer-expression )
Parameters

  • 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 about casting data types, see CAST function [Data type conversion].

Returns

INT

TIMESTAMP

Remarks

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

Standards and compatibility

  • SQL/2003   Vendor extension.

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 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' );
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1