Skip to main content
Previous sectionNext section

HOUR

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

Synopsis

{fn HOUR(time-expression)}

Arguments

Argument Description
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.

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 window (an encoded 64-bit signed integer), or data type %Library.TimeStampOpens in a new window (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 window method call:

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

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
Copy code to clipboard
SELECT {fn HOUR(67538)} AS HorologHour
Copy code to clipboard

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
Copy code to clipboard

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
Copy code to clipboard

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
Copy code to clipboard

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')})
Copy code to clipboard

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

  DO ##class(%SYS.NLS.Format).SetFormatItem("TimeSeparator",".")
  &sql(SELECT {fn HOUR('2018-02-16 18.45.38')} INTO :a)
  WRITE "hour=",a
Copy code to clipboard

See Also

FeedbackOpens in a new window