HEXTR: Extracting Components of a Date-Time Value and Setting Remaining Components to Zero

How to:

Available Languages: reporting, Maintain

The HEXTR function extracts one or more components from a date-time value and moves them to a target date-time field with all other components set to zero.

Syntax: How to Extract Multiple Components From a Date-Time Value

HEXTR(datetime, 'componentstring', length, output)

where:

datetime
Date-time

Is the date-time value from which to extract the specified components.

componentstring
Alphanumeric

Is a string of codes, in any order, that indicates which components are to be extracted and moved to the output date-time field. The following table shows the valid values. The string is considered to be terminated by any character not in this list:

Code

Description

C

century (the two high-order digits only of the four-digit year)

Y

year (the two low-order digits only of the four-digit year)

YY

Four digit year.

M

month

D

day

H

hour

I

minutes

S

seconds

s

milliseconds (the three high-order digits of the six-digit microseconds value)

u

microseconds (the three low-order digits of the six-digit microseconds value)

m

All six digits of the microseconds value.

n

Low order three digits of nine decimal digits.

length

Is the length of the returned date-time value. Valid values are:

  • 8 indicates a time value that includes milliseconds.
  • 10 indicates a time value that includes microseconds.
  • 12 indicates a time value that includes nanoseconds.
output

Is the field that contains the result, or the format of the output value enclosed in single quotation marks. This field must be in date-time format (data type H).

Example: Extracting Hour and Minute Components Using HEXTR

The VIDEOTR2 data source has a date-time field named TRANSDATE of type HYYMDI. The following request selects all records containing the time 09:18AM, regardless of the value of the remaining components:

TABLE FILE VIDEOTR2
PRINT TRANSDATE
BY LASTNAME
BY FIRSTNAME
WHERE HEXTR(TRANSDATE, 'HI', 8, 'HYYMDI') EQ DT(09:18AM)
END

The output is:

LASTNAME         FIRSTNAME   TRANSDATE
--------         ---------   ---------
DIZON            JANET       1999/11/05 09:18
PETERSON         GLEN        1999/09/09 09:18