Skip to main content

This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.

For information on migrating to InterSystems IRIS, see the InterSystems IRIS Migration Guide and Migrating to InterSystems IRIS, both available on the WRC Distributions page (login required).


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


{fn HOUR(time-expression)}


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 or %TimeStamp.


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 is data type %Library.TimeStamp (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-expression can also be specified as data type %Library.FilemanDate, %Library.FilemanTimestamp, or %MV.Date.

Hours are expressed in 24-hour time. The hours (hh) portion should be an integer in the range from 0 through 23. There is, however, no range checking for user-supplied values. Numbers greater than 23, negative numbers, and fractions are returned as specified. 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', '00', or a nonnumeric value. 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() method call:



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

SELECT {fn HOUR('2000-02-16 18:45:38')} AS Hour_Given
Copy code to clipboard
SELECT {fn HOUR(67538)} AS Hour_Given
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('2000-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('2000-02-16')} AS Hour_Given
Copy code to clipboard

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

       {fn HOUR({fn CURTIME()})} AS H_CurT,
       {fn HOUR({fn NOW()})} AS H_Now,
       {fn HOUR($HOROLOG)} AS H_Horolog,
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('2004-02-05 11:45')}),
       LENGTH({fn HOUR('2004-02-15 03:45')}),
       LENGTH({fn HOUR('2004-02-15 3:45')}),
       LENGTH({fn HOUR('2004-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('2000-02-16 18.45.38')} INTO :a)
  WRITE "hour=",a
Copy code to clipboard

See Also