HOURS function [Date and time]

The HOURS function manipulates a TIMESTAMP, or returns the number of hours between two TIMESTAMP values. For specific details, see this function's usage.

Syntax 1

HOURS ( timestamp-expression )

Syntax 2

HOURS ( timestamp-expression, timestamp-expression )

Syntax 3

HOURS ( time-or-timestamp-expression, integer-expression )

Parameters

time-or-timestamp-expression A value of type TIME or TIMESTAMP.
timestamp-expression A value of type TIMESTAMP.
integer-expression The number of hours to be added to time-or-timestamp-expression. If integer-expression is negative, the appropriate number of hours is subtracted from time-or-timestamp-expression.
For information about casting data types, see CAST function [Data type conversion].

Returns

Remarks

The result of the HOURS function depends on its arguments.

Syntax 1 If you pass a single timestamp-expression to the HOURS function, it will return the number of hours between midnight 0000-02-29 and timestamp-expression as an INTEGER.

Note

0000-02-29 is not meant to imply an actual date; it is the default TIMESTAMP value used by the HOURS function.
Syntax 2 If you pass two TIMESTAMP values to the HOURS function, the function returns the integer number of hours between them.
Syntax 3 If you pass a TIMESTAMP value and an INTEGER value to the HOURS function, the function returns the TIMESTAMP result of adding the integer number of hours to time-or-timestamp-expression argument. Similarly, if you pass a TIME value as the first argument, a TIME value is returned as the result. Syntax 3 does not support implicit conversion of the first argument. It may be necessary to explicitly cast the first argument to a DATE, TIME or TIMESTAMP value. If the first argument is a DATE, midnight is assumed for the time portion.

Instead of Syntax 2, use the DATEDIFF function. Instead of Syntax 3, use the DATEADD function.

Standards and compatibility

Example

The following statements return the value 4, signifying that the second TIMESTAMP value is four hours after the first. It is recommended that you use the second example (DATEDIFF).

SELECT HOURS( '1999-07-13 06:07:12', '1999-07-13 10:07:12' );

SELECT DATEDIFF( hour, '1999-07-13 06:07:12', '1999-07-13 10:07:12' );

The following statement returns the value 17517342.

SELECT HOURS( '1998-07-13 06:07:12' );

The following statements return the datetime 1999-05-13 02:05:07.000. It is recommended that you use the second example (DATEADD).

SELECT HOURS( CAST( '1999-05-12 21:05:07' AS DATETIME ), 5 );

SELECT DATEADD( hour, 5, '1999-05-12 21:05:07' );

Discuss this page in DocCommentXchange.

HOURS function [Date and time]

Note