Start of change

ADD_DAYS scalar function

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

FL 507

Start of change
Passthrough-only expression: This function is passthrough-only and cannot run on Db2 for z/OS without acceleration. For information about invoking this function, see Accelerating queries with passthrough-only expressions.
End of change
Read syntax diagramSkip visual syntax diagramADD_DAYS(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 WITHOUT TIME ZONE, CHAR, VARCHAR, 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 and does not contain a time zone.
numeric-expression
An expression that specifies the number of days to add to the starting date specified by expression. The expression must return a value that is a built-in numeric, CHAR, VARCHAR, 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 days.

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.

Examples

  • Assume that 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_DAY 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