Writing Date-Time Expressions

In this section:

Date-time values for Maintain may be supplied in one of the following ways:

Maintain supports the date-time data type with the following restrictions:

A date-time constant in a Maintain procedure, and in an IF expression in a report procedure, has one of the following formats (note that in a report procedure's IF expression, if the value contains no blanks or special characters, the single quotation marks are not necessary)

'date_string  [time_string]'
'time_string  [date_string]'

where:

time_string

Cannot contain blanks. Time components are separated by colons and may be followed by AM, PM, am, or pm. For example:

14:30:20:99       (99 milliseconds)
14:30
14:30:20.99       (99/100 seconds)
14:30:20.999999   (999999 microseconds)
02:30:20:500pm

Note that seconds can be expressed with a decimal point or be followed by a colon.

date_string

Can have one of the following three formats:

Note:


Top of page

Example: Using a Date-Time Value in a COMPUTE Command
COMPUTE RAISETIME/HYYMDIA = '20000101 09:00AM'; 

Top of page

x
Manipulating Date-Time Values Directly

The only direct operations that can be performed on date-time variables and constants are comparison using a logical expression and simple assignment of the form A = B. All other operations are accomplished through a set of date-time subroutines. For more information see Writing Alphanumeric Expressions.


Top of page

x
Comparing and Assigning Date-Time Values

Any two date-time values can be compared, even if their lengths do not match.

If a date-time field supports missing values, fields that contain the missing value have a greater value than any date-time field can have. Therefore, in order to exclude missing values from report output when using a GT or GE operator in a selection test, it is recommended that you add the additional constraint field NE MISSING to the selection test:

date_time_field {GT|GE} date_time_value AND date_time_field NE MISSING

Assignments are permitted between date-time formats of equal or different lengths. Assigning a 10-byte date-time value to an 8-byte date-time value truncates the microsecond portion (no rounding takes place). Assigning a short value to a long one sets the low-order three digits of the microseconds to zero.

Other operations, including arithmetic, concatenation, and the reporting operators EDIT and LIKE on date-time operands are not supported. Reporting prefix operators that work with alphanumeric fields are supported.



Example: Testing for Missing Date-Time Values

Consider the DATETIM2 Master File:

FILE=DATETIM2,  SUFFIX=FOC                ,$
SEGNAME=DATETIME, SEGTYPE=S0              ,$
FIELD=ID, ID, USAGE = I2                  ,$
FIELD=DT1, DT1,   USAGE=HYYMDS, MISSING=ON,$

Field DT1 supports missing values. Consider the following request:

TABLE FILE DATETIM2
PRINT ID DT1
END

The resulting report output shows that in the instance with ID=3, the field DT1 has a missing value:

ID  DT1
--  ---
 1  2000/01/01 02:57:25
 2  1999/12/31 00:00:00
 3  .

The following request selects values of DT1 that are greater than 2000/01/01 00:00:00 and are not missing:

TABLE FILE DATETIM2
PRINT ID DT1
  WHERE DT1 NE MISSING AND DT1 GT DT(2000/01/01 00:00:00);
END

The missing value is not included in the report output:

ID  DT1
--  ---
 1  2000/01/01 02:57:25

Top of page

x
Date-Time Subroutines

Reference:

The following subroutines allow you to manipulate date-time values:

Function Name

Description

HCNVRT

Converts date-time values to alphanumeric format for use with operators such as EDIT, CONTAINS, and LIKE.

HINPUT

Converts an alphanumeric string to a date-time value.

HADD

Increments date-time values by a specified number of units.

HDIFF

Returns the number of units of a specific date-time component between two date-time values.

HNAME

Extracts specified components of a date-time value and converts them to alphanumeric format.

HPART

Extracts a component of a date-time value in numeric format.

HSEPTPT

Inserts the numeric value of a specified component in a date-time field.

HMIDNT

Changes the time portion of a date-time field to midnight.

HDATE

Extracts the date components from a date-time field and converts them to a date field.

HDTTM

Converts a date field to a date-time field with the time set to midnight.

HTIME

Extracts all of the time components from a date-time field and converts them to a number of milliseconds or microseconds in numeric format.

HGETC

Returns the current date and time in date-time format.



x
Reference: Notes Regarding ISO Standard Date-Time Representations

International Standard ISO 8601 describes the standards for numeric representations of date and time. Some of the relevant standards and notes about their implementation follow:


Information Builders