Start of change

ADD_DAYS

Edit online

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

Read syntax diagramSkip visual syntax diagramADD_DAYS(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 days 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 days.

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.

Examples

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

    The host variable ADD_DAT is set with the value representing 2007-02-01.

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

    The host variable ADD_DAY is set with the value representing the day plus 3 days, 1965-07-30.

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

    In both cases, the host variable DATEHV is set with the value '2008-03-03'.

    Now consider the same examples but with the date '2008-2-29' as the argument. 

    SET :DATEHV = DATE('2008-2-29') + 4 DAYS
SET :DATEHV = ADD_DAYS('2008-2-29', 4)

    In both cases, the host variable DATEHV is set with the value '2008-03-04'.

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

    The host variable ADD_DAY is set to 1965-07-24; the value representing July 27, 1965 minus 3 days.

End of change