HOURS function [Date and time]

A function that evaluates hours. For specific details, see this function's usage.

Syntax 1: integer

HOURS ( [ datetime-expression, ] datetime-expression )

Syntax 2: timestamp

HOURS ( datetime-expression, integer-expression )

Parameters

datetime-expression A date and time.
integer-expression The number of hours to be added to the datetime-expression. If integer-expression is negative, the appropriate number of hours is subtracted from the datetime. If you supply an integer expression, the datetime-expression must be explicitly cast as a DATETIME data type.
For information about casting data types, see CAST function [Data type conversion].

Remarks

The behavior of this function can vary depending on what you supply:

If you give a single date, this function returns the number of hours since 0000-02-29.

Note

0000-02-29 is not meant to imply an actual date; it is the date used by the date algorithm.
If you give two time stamps, this function returns the integer number of hours between them. Instead, use the DATEDIFF function.
If you give a date and an integer, this function adds the integer number of hours to the specified timestamp. Instead, use the DATEADD function.

Standards and compatibility

SQL/2003 Vendor extension.

Example

The following statements return the value 4, signifying that the second timestamp 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' );

Send feedback about this page via email or DocCommentXchange