ADD_HOURS scalar function

The ADD_HOURS function returns a timestamp value that represents the first argument plus a specified number of hours.

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

The schema is SYSIBM.

expression
An expression that specifies the starting timestamp. The expression must return a value that is a 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. The expression must not return a value that is a DATE (SQLSTATE 42815). 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 hours to add to the starting timestamp 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 hours.

The result of the function is a timestamp with the same precision as expression, if expression is a timestamp. Otherwise, the result is a TIMESTAMP(12). If any argument can be null, the result can be null; if any argument is null, the result is the null value.

Examples

  1. Assume that the current timestamp is January 31, 2007, 01:02:03.123456. Set the host variable ADD_HOUR with the current timestamp plus 1 hour.
       SET :ADD_HOUR = ADD_HOURS(CURRENT TIMESTAMP, 1)
    The host variable ADD_HOUR is set with the value representing 2007-01-31-02.02.03.123456.
  2. Assume that TIMESTAMP is a host variable with the value July 27, 1965 23:58:59. Set the host variable ADD_HOUR with the value of that timestamp plus 3 hours. 
       SET :ADD_HOUR = ADD_HOURS(:TIMESTAMP,3)
    The host variable ADD_HOUR is set with the value representing the timestamp plus 3 hours, 1965-07-28-02.58.59.
  3. The ADD_HOURS function and datetime arithmetic can be used to achieve the same results. The following examples demonstrate this.
       SET :TIMESTAMPHV = TIMESTAMP '2008-2-28-22.58.59' + 4 HOURS
   SET :TIMESTAMPHV = ADD_HOURS( TIMESTAMP '2008-2-28-22.58.59', 4)
    In both cases, the host variable TIMESTAMPHV is set with the value '2008-02-29-02.58.59'.
    Now consider the same examples but with 28 hours added. 
       SET :TIMESTAMPHV = TIMESTAMP '2008-2-28-22.58.59' + 28 HOURS
   SET :TIMESTAMPHV = ADD_HOURS(TIMESTAMP '2008-2-28-22.58.59', 28)
    In both cases, the host variable TIMESTAMPHV is set with the value '2008-03-01-02.58.59'.
  4. Assume that TIMESTAMP is a host variable with the value July 27, 1965 23:58:59. Set the host variable ADD_HOUR with the value of that timestamp minus 3 hours. 
       SET :ADD_HOUR = ADD_HOURS(:TIMESTAMP,-3)
    The host variable ADD_HOUR is set to 1965-07-27-20.58.59; the value representing July 27, 1965 23:58:59 minus 3 hours.