HOUR (SQL)

A time function that returns the hour for a datetime expression.

Synopsis

{fn HOUR(time-expression)}

Description

HOUR returns an integer specifying the hour for a given time or datetime value. The hour is calculated for a $HOROLOG or $ZTIMESTAMP value, an ODBC format date string, or a timestamp.

A time-expression timestamp can be either data type %Library.PosixTimeOpens in a new tab (an encoded 64-bit signed integer), or data type %Library.TimeStampOpens in a new tab (yyyy-mm-dd hh:mm:ss.fff).

To change this default time format, use the SET OPTION command.

Note that you can supply a time integer (number of elapsed seconds), but not a time string (hh:mm:ss). You must supply a datetime string (yyyy-mm-dd hh:mm:ss). You can omit the seconds (:ss) or minutes and seconds (mm:ss) portion of a datetime string and still return the hour portion. The time portion of a datetime string must be a valid time value. The date portion of the datetime string is not validated.

Hours are expressed in 24-hour time. The hours (hh) portion should be an integer in the range from 0 through 23. Leading zeros are optional on input; leading zeros are suppressed on output.

HOUR returns a value of 0 hours when the hours portion is '0' or '00'. Zero hours is also returned if no time expression is supplied, or if the hours portion of the time expression is omitted (':mm:ss' or '::ss').

The same time information can be returned using DATEPART or DATENAME.

This function can also be invoked from ObjectScript using the HOUR()Opens in a new tab method call:

$SYSTEM.SQL.Functions.HOUR(time-expression)

Arguments

time-expression

An expression that is the name of a column, the result of another scalar function, or a string or numeric literal. It must resolve either to a datetime string or a time integer, where the underlying data type can be represented as %Time, %TimeStamp, or %PosixTime.

Examples

The following examples both return the number 18 because the time-expression value is 18:45:38:

SELECT {fn HOUR('2017-02-16 18:45:38')} AS ODBCHour

SELECT {fn HOUR(67538)} AS HorologHour

The following example also returns 18. The seconds (or minutes and seconds) portion of the time value can be omitted.

SELECT {fn HOUR('2017-02-16 18:45')} AS Hour_Given

The following example returns 0 hours, because the time portion of the datetime string has been omitted:

SELECT {fn HOUR('2017-02-16')} AS Hour_Given

The following examples all return the hours portion of the current time:

SELECT {fn HOUR(CURRENT_TIME)} AS H_CurrentT,
       {fn HOUR({fn CURTIME()})} AS H_CurT,
       {fn HOUR({fn NOW()})} AS H_Now,
       {fn HOUR($HOROLOG)} AS H_Horolog,
       {fn HOUR($ZTIMESTAMP)} AS H_ZTS

Note that $ZTIMESTAMP returns Coordinated Universal Time (UTC). The other time-expression values return the local time.

The following example shows that leading zeros are suppressed. The first HOUR function returns a length 2, the others return a length of 1. An omitted time is considered to be 0 hours, which has a length of 1:

SELECT LENGTH({fn HOUR('2018-02-15 11:45')}),
       LENGTH({fn HOUR('2018-02-15 03:45')}),
       LENGTH({fn HOUR('2018-02-15 3:45')}),
       LENGTH({fn HOUR('2018-02-15')})

The following example shows that the HOUR function recognizes the TimeSeparator character specified for the locale:

SELECT {fn HOUR('2018-02-16 18.45.38')}