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

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 is 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-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()Opens in a new tab method call:

$SYSTEM.SQL.HOUR(time-expression)

Examples

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

SELECT {fn HOUR(67538)} AS Hour_Given

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

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

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('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')})

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

HOUR

Synopsis

Arguments

Description

Examples

See Also