ADD_YEARS scalar function

The ADD_YEARS function returns a datetime value that represents the first argument plus a specified number of years.

Read syntax diagramSkip visual syntax diagramADD_YEARS(expression, numeric-expression)

The schema is SYSIBM.

expression
An expression that specifies the starting date. The expression must return a value that is a DATE, TIMESTAMP, CHAR, or VARCHAR data type. In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type. CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting. If expression is a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it must be a valid string that is accepted by the TIMESTAMP scalar function.
numeric-expression
An expression that specifies the number of years to add to the starting date specified by expression. The expression must return a value that is a built-in numeric, CHAR, or VARCHAR data type. In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type. CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting. If the expression is not an INTEGER, it is cast to INTEGER before the function is evaluated. A negative numeric value can be used to subtract years.

The result of the function is a timestamp with the same precision as expression, if expression is a timestamp. Otherwise, the result of the function is a date. If any argument can be null, the result can be null; if any 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

  1. Assume that 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.
  2. 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.
  3. 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'.
  4. 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.