Writing Date-Time Expressions

In this section:

Manipulating Date-Time Values Directly
Comparing and Assigning Date-Time Values
Date-Time Subroutines

How to:

Write Date-Time Expressions

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

As a value in a computed expression, enclosed in single quotation marks (') or double quotation marks (").
As a value extracted or computed by a date-time function.
Using an application Winform.

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

The default date-time format separators (/) must be used. Other separators are not supported.
When you create a WHERE statement or an IF THEN ELSE clause, you must use a variable as the test value.
The format SET DATEFORMAT, used to change the default input format, is not supported.
The SET commands WEEKFIRST and DTSTRICT are not supported.
Computing an expression to DT (value) is not supported.

Syntax: How to Write Date-Time Expressions

A date-time constant in a Maintain Data procedure, and in an IF expression in a report procedure, has one of the following formats.

Note: In an 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.

If there is a colon after seconds, the value following it represents milliseconds. There is no way to express microseconds using this notation.
A decimal point in the seconds value indicates the decimal fraction of a second. Microseconds can be represented using six decimal digits.

date_string

Can have one of the following three formats:

Numeric string format. Is exactly four, six, or eight digits. Four-digit strings are considered to be a year (century must be specified). The month and day are set to January 1. Six-digit and eight-digit strings contain two or four digits for the year, followed by two for the month, and then two for the day.
If a numeric-string format longer than eight digits is encountered, it is treated as a combined date-time string in the Hn format described in the Describing Data With WebFOCUS Language manual. The following are examples of numeric string date constants:
```
99
1999
19990201
```
Formatted-string format. Contains a one-digit or two-digit day, a one-digit or two-digit month, and a two-digit or four-digit year separated by spaces, slashes, hyphens, or periods. If any of the three fields is four digits, it is interpreted as the year, and the other two fields must follow the order given by the DATEFORMAT setting. The following are examples of formatted-string date constants:
```
1999/05/20
5 20 1999
99.05.20
1999-05-20
```
Translated-string format. Contains the full or abbreviated month name. The year must also be present in four-digit or two-digit form. If the day is missing, day 1 of the month is assumed. If the day is present, it can have one or two digits. If the string contains both a two-digit year and a two-digit day, they must be in the order given by the DATEFORMAT setting. For example:
```
January 6 2000
```

Note:

The date and time strings must be separated by at least one blank space. Blank spaces are also permitted at the beginning and end of the date-time string.
In each date format, two-digit years are interpreted using the [F]DEFCENT and [F]YRTHRESH settings.

Example: Using a Date-Time Value in a COMPUTE Command

COMPUTE RAISETIME/HYYMDIA = '20000101 09:00AM';

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 Character Expressions.

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

Date-Time Subroutines

Reference:

Notes Regarding ISO Standard Date-Time Representations

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

Function Name	Description
HADD	Increments date-time values by a specified number of units.
HCNVRT	Converts date-time values to alphanumeric format for use with operators, such as EDIT, CONTAINS, and LIKE.
HDATE	Extracts the date components from a date-time field and converts them to a date field.
HDIFF	Returns the number of units of a specific date-time component between two date-time values.
HDTTM	Converts a date field to a date-time field with the time set to midnight.
HEXTR	Extracts components from a date-time value and moves them to a target date-time field with all other components set to zero.
HGETC	Returns the current date and time in date-time format.
HMASK	Extracts components from a date-time value and moves them to a target date-time field with all other components of the target field preserved.
HHMMSS	Retrieves the current time from the system.
HINPUT	Converts an alphanumeric string to a date-time value.
HMIDNT	Changes the time portion of a date-time field to midnight.
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.
HSETPT	Inserts the numeric value of a specified component in a date-time field.
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.
HTMTOTS/TIMETOTS	Converts a time to a timestamp.

For more information on these functions, see the Using Functions manual.

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:

The international standard date notation is YYYY-MM-DD.
The international standard for the first day of a week is Monday. You can use the WEEKFIRST parameter with App Studio procedures to control the day used as the first day of the week by the date-time functions. However, Maintain Data does not support this setting.
The standard specifies that week 1 of a year is the first week of the year that has a Thursday. Combined with the standard of Monday as day 1, this rule ensures that week 1 has at least four of its days in the specified year.
The following rules represent an extension to the standard in this implementation:
- Whatever day you choose for your WEEKFIRST setting, the date-time functions define week 1 as the first week with at least four days in the specified year.
- With these rules, it is possible for the first few days of January to fall in the week prior to week 1. The international standard considers these dates to be in week 53 of the previous year. However, the date-time functions return zero for the week component when it falls in the week prior to week 1.
The international standard notation for the time of day is hh:mm:ss using the 24-hour system. However, the date-time data type and date-time functions allow you to use the 12-hour system.