Start of change

ADD_YEARS

Edit online

The ADD_YEARS function returns a date or timestamp that represents the first argument plus a specified number of years.

Read syntax diagramSkip visual syntax diagramADD_YEARS(expression ,numeric-expression)
expression
An expression that specifies the starting date. The expression must return a value that is of one of the following built-in data types: a date, a timestamp, a character string, or a graphic string.

If expression is a character or graphic string, its value must be a valid string representation of a date or timestamp. For the valid formats of string representations of dates and timestamps, see String representations of datetime values.

numeric-expression
An expression that specifies the number of years to add to expression. numeric-expression must return a value that is a built-in numeric data type. If the data type of the expression is not BIGINT, it is implicitly cast to BIGINT before evaluating the function. A negative numeric value can be used to subtract years.

If expression is a timestamp, the result of the function is a timestamp with the same precision as expression. Otherwise, the result of the function is a date. If either argument can be null, the result can be null; if either argument is null, the result is the null value.

If the result would be February 29 of a non-leap-year, the day is changed to 28, and a warning indicator in the SQLCA is set to indicate the adjustment.

Examples

  • Assume today is January 31, 2007. Set the host variable ADD_YEAR with the current day plus 1 year.
    SET :ADD_YEAR = ADD_YEARS(CURRENT_DATE, 1)

    The host variable ADD_YEAR is set with the value representing 2008-01-31.

  • Assume that DATE is a host variable with the value July 27, 1965. Set the host variable ADD_YEAR with the value of that day plus 3 years.
    SET :ADD_YEAR = ADD_YEARS(:DATE,3)

    The host variable ADD_YEAR is set with the value representing the day plus 3 years, 1968-07-27.

  • The ADD_YEARS function and datetime arithmetic can be used to achieve the same results. The following examples demonstrate this.
    SET :DATEHV = DATE('2008-2-29') + 4 YEARS
SET :DATEHV = ADD_YEARS('2008-2-29', 4)

    In both cases, the host variable DATEHV is set with the value '2012-02-29'.

    Now consider the same examples but with 3 years added. 

    SET :DATEHV = DATE('2008-2-29') + 3 YEARS
SET :DATEHV = ADD_YEARS('2008-2-29', 3)

    In both cases, the host variable DATEHV is set with the value '2011-02-28'.

  • Assume that DATE is a host variable with the value July 27, 1965. Set the host variable ADD_YEAR with the value of that day minus 3 years.
    SET :ADD_YEAR = ADD_YEARS(:DATE,-3)

    The host variable ADD_YEAR is set to 1962-07-27; the value representing July 27, 1965 minus 3 years.

End of change