ADD_YEARS
The ADD_YEARS function returns a date or timestamp that represents the first argument plus a specified number of years.
- 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.