Start of change

ADD_HOURS

Edit online

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

Read syntax diagramSkip visual syntax diagramADD_HOURS(expression ,numeric-expression)
expression
An expression that specifies the starting timestamp. The expression must return a value that is of one of the following built-in data types: 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 timestamp. For the valid formats of string representations of timestamps, see String representations of datetime values.

numeric-expression
An expression that specifies the number of hours 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 hours.

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 TIMESTAMP(12). If either argument can be null, the result can be null; if either argument is null, the result is the null value.

Examples

  • 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.

  • 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'.

  • The ADD_HOURS function and datetime arithmetic can be used to achieve the same results. The following examples demonstrate this.
    SET :TIMESTAMPHV = TIMESTAMP '2008-02-28 22:58:59' + 4 HOURS
SET :TIMESTAMPHV = ADD_HOURS( TIMESTAMP '2008-02-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-02-28 22:58:59' + 28 HOURS
SET :TIMESTAMPHV = ADD_HOURS(TIMESTAMP '2008-02-28 22:58:59', 28)

    In both cases, the host variable TIMESTAMPHV is set with the value '2008-03-01 02:58:59'.

  • 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.

End of change