HOUR scalar function

The HOUR function returns the hour part of a value.

Read syntax diagramSkip visual syntax diagramHOUR(expression)

The schema is SYSIBM.

expression
An expression that returns a value of one of the following built-in data types: a DATE, a TIME, a TIMESTAMP, a character string, or an exact numeric data type.

If expression is a character string, it must not be a CLOB and its value must be a valid string representation of a datetime value. For the valid formats of string representations of datetime values, see String representations of datetime values in Datetime values.

If expression is an exact numeric value, it must be a time duration or timestamp duration. For information about valid time durations and timestamp durations, see Datetime operands and durations.

Only Unicode databases support an expression that is a valid graphic string representation of a datetime value that is not a DBCLOB. The graphic string is converted to a character string before the function is executed.

The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.

The other rules depend on the data type of the argument:
  • If the argument is a TIME, TIMESTAMP or valid string representation of a time or timestamp:
    • The result is the hour part of the value, which is an integer between 0 and 24.
  • If the argument is a DATE or valid string representation of a date:
    • The result is 0.
  • If the argument is a time duration or timestamp duration:
    • The result is the hour part of the value, which is an integer between -99 and 99. A nonzero result has the same sign as the argument.

Example

Using the CL_SCHED sample table, select all the classes that start in the afternoon. 
   SELECT * FROM CL_SCHED
     WHERE HOUR(STARTING) BETWEEN 12 AND 17