Using Date-Time Functions

Date-Time Parameters

In this section:

Specifying the Order of Date Components
Specifying the First Day of the Week for Use in Date-Time Functions
Controlling Processing of Date-Time Values

The DATEFORMAT parameter specifies the order of the date components for certain types of date-time values. The WEEKFIRST parameter specifies the first day of the week. The DTSTRICT parameter determines the extent to which date-time values are checked for validity.

Specifying the Order of Date Components

How to:

Specify the Order of Date Components in a Date-Time Field

The DATEFORMAT parameter specifies the order of the date components (month/day/year) when date-time values are entered in the formatted string and translated string formats described in Using Date-Time Formats. It makes the input format of a value independent of the format of the variable to which it is being assigned.

Syntax: How to Specify the Order of Date Components in a Date-Time Field

SET DATEFORMAT = option

where:

option: Can be one of the following: MDY, DMY, YMD, or MYD. MDY is the default value for the U.S. English format.

Example: Using the DATEFORMAT Parameter

The following request uses a natural date literal with ambiguous numeric day and month components (APR 04 05) as input to the HINPUT function:

SET DATEFORMAT = MYD
DEFINE FILE EMPLOYEE
DTFLDYYMD/HYYMDI =  HINPUT(9,'APR 04 05', 8, DTFLDYYMD);
END
TABLE FILE EMPLOYEE
SUM   CURR_SAL NOPRINT DTFLDYYMD
END

With DATEFORMAT set to MYD, the value is interpreted as April 5, 1904:

DTFLDYYMD
---------
1904-04-05 00:00

Specifying the First Day of the Week for Use in Date-Time Functions

How to:

Set a Day as the Start of the Week
View the Current Setting of WEEKFIRST

The WEEKFIRST parameter specifies a day of the week as the start of the week. This is used in week computations by the HADD, HDIFF, HNAME, HPART, and HYYWD functions. It is also used by the DTADD, DTDIFF, DTRUNC, and DTPART functions. The default values are different for these functions, as described in Set a Day as the Start of the Week. The WEEKFIRST parameter does not change the day of the month that corresponds to each day of the week, but only specifies which day is considered the start of the week.

The HPART, DTPART, HYYWD, and HNAME subroutines can extract a week number from a date-time value. To determine a week number, they can use different definitions. For example, ISO 8601 standard week numbering defines the first week of the year as the first week in January with four or more days. Any preceding days in January belong to week 52 or 53 of the preceding year. The ISO standard also establishes Monday as the first day of the week.

You specify which type of week numbering to use by setting the WEEKFIRST parameter, as described in Set a Day as the Start of the Week.

Since the week number returned by HNAME, DTPART, and HPART functions can be in the current year or the year preceding or following, the week number by itself may not be useful. The function HYYWD returns both the year and the week for a given date-time value.

Syntax: How to Set a Day as the Start of the Week

SET WEEKFIRST = value

where:

value

Can be:

1 through 7, representing Sunday through Saturday with non-standard week numbering.
Week numbering using these values establishes the first week in January with seven days as week number 1. Preceding days in January belong to the last week of the previous year. All weeks have seven days.
ISO1 through ISO7, representing Sunday through Saturday with ISO standard week numbering.
Note: ISO is a synonym for ISO2.

Week numbering using these values establishes the first week in January with at least four days as week number 1. Preceding days in January belong to the last week of the previous year. All weeks have seven days.
STD1 through STD7, in which the digit 1 (Sunday) through 7 (Saturday) indicates the starting day of the week.
Note: STD without a digit is equivalent to STD1.

Week numbering using these values is as follows. Week number 1 begins on January 1 and ends on the day preceding the first day of the week. For example, for STD1, the first week ends on the first Saturday of the year. The first and last week may have fewer than seven days.
SIMPLE, which establishes January 1 as the start of week 1, January 8 is the start of week 2, and so on. The first day of the week is, thus, the same as the first day of the year. The last week (week 53) is either one or two days long.
0 (zero), is the value of the WEEKFIRST setting before the user issues an explicit WEEKFIRST setting. The date-time functions HPART, HNAME, HYYWD, HADD, and HDIFF use Saturday as the start of the week, when the WEEKFIRST setting is 0. The simplified functions DTADD, DTDIFF, DTRUNC, and DTPART, as well as printing of dates truncated to weeks, and recognition of date constant strings that contain week numbers, use Sunday as the default value, when the WEEKFIRST setting is 0. If the user explicitly sets WEEKFIRST to another value, that value is used by all of the functions.

Example: Setting Sunday as the Start of the Week

The following designates Sunday as the start of the week, using non-standard week numbering:

SET WEEKFIRST = 1

Syntax: How to View the Current Setting of WEEKFIRST

? SET WEEKFIRST

This returns the value that indicates the week numbering algorithm and the first day of the week. For example, the integer 1 represents Sunday with non-standard week numbering.

Controlling Processing of Date-Time Values

How to:

Enable Strict Processing of Date-Time Values

Strict processing checks date-time values when they are input by an end user, read from a transaction file, displayed, or returned by a subroutine to ensure that they represent a valid date and time. For example, a numeric month must be between 1 and 12, and the day must be within the number of days for the specified month.

Syntax: How to Enable Strict Processing of Date-Time Values

SET DTSTRICT = {ON|OFF}

where:

ON

Invokes strict processing. ON is the default value.

If DTSTRICT is ON and the result would be an invalid date-time value, the function returns the value zero (0).

OFF

Does not invoke strict processing. Date-time components can have any value within the constraint of the number of decimal digits allowed in the field. For example, if the field is a two-digit month, the value can be 12 or 99, but not 115.

Supplying Arguments for Date-Time Functions

Reference:

Arguments for Use With Date and Time Functions

Date-time functions may operate on a component of a date-time value. This topic lists the valid component names and abbreviations for use with these functions.

Reference: Arguments for Use With Date and Time Functions

The following component names, valid abbreviations, and values are supported as arguments for the date-time functions that require them:

Component Name	Abbreviation	Valid Values
year	yy	0001-9999
quarter	qq	1-4
month	mm	1-12 or a month name, depending on the function.
day-of-year	dy	1-366
`day` or `day-of-month`	dd	1-31 (The two component names are equivalent.)
week	wk	1-53
weekday	dw	1-7 (Sunday-Saturday)
hour	hh	0-23
minute	mi	0-59
second	ss	0-59
millisecond	ms	0-999
microsecond	mc	0-999999
nanosecond	ns	0-999999999

Note:

For an argument that specifies a length of eight, ten, or 12 characters, use eight to include milliseconds, ten to include microseconds, and 12 to include nanoseconds in the returned value.
The last argument is always a USAGE format that indicates the data type returned by the function. The type may be A (alphanumeric), I (integer), D (floating-point double precision), H (date-time), or a date format (for example, YYMD).

Using Date-Time Formats

In this section:

Numeric String Format
Formatted-string Format
Translated-string Format
Time Format

There are three types of date formats that are valid in date-time values: numeric string format, formatted-string format, and translated-string format. In each format, two-digit years are interpreted using the DEFCENT and YRTHRESH parameters.

Time components are separated by colons and may be followed by A.M., P.M., a.m., or p.m.

The DATEFORMAT parameter specifies the order of the date components (month/day/year) when date-time values are entered in the formatted string and translated string formats. It makes a value’s input format independent of the format of the variable to which it is being assigned.

Numeric String Format

The numeric string format is exactly two, four, six, or eight digits. Four-digit strings are considered to be a year (century must be specified), and the month and day are set to January 1. Six and eight-digit strings contain two or four digits for the year, followed by two for the month, and two for the day. Because the component order is fixed with this format, the DATEFORMAT setting is ignored.

If a numeric-string format longer than eight digits is encountered, it is treated as a combined date-time string in the Hnn format.

Example: Using Numeric String Format

The following are examples of numeric string date constants:

String	Date
99	January 1, 1999
1999	January 1, 1999
19990201	February 1, 1999

Formatted-string Format

The formatted-string format contains a one or two-digit day, a one or two-digit month, and a two or four-digit year, each component separated by a space, slash, hyphen, or period. All three components must be present and follow the DATEFORMAT setting. 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.

Example: Using Formatted-string Format

The following are examples of formatted-string date constants and specify May 20, 1999:

Translated-string Format

The 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 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.

Example: Using Translated-string Format

The following date is in translated-string format:

January 6 2000

Time Format

Time components are separated by colons and may be followed by A.M., P.M., a.m., or p.m.

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 or nanoseconds 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. Nanoseconds can be represented using nine decimal digits.

Example: Using Time Formats

The following are examples of acceptable time formats:

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

Example: Using Universal Date-Time Input Values

With DTSTANDARD settings of STANDARD and STANDARDU, the following date-time values can be read as input:

Input Value	Description
14:30[:20,99]	Comma separates time components instead of period
14:30[:20.99]Z	Universal time
15:30[:20,99]+01 15:30[:20,99]+0100 15:30[:20,99]+01:00	Each of these is the same as above in Central European Time
09:30[:20.99]-05	Same as above in Eastern Standard Time

Note that these values are stored identically internally with the STANDARDU setting. With the STANDARD setting, everything following the Z, +, or - is ignored.

Assigning Date-Time Values

How to:

Assign Date-Time Values

A date-time value is a constant in character format assigned by one of the following:

A sequential data source.
An expression that defines WHERE or IF criteria or creates a temporary field using the DEFINE or COMPUTE command.

A date-time constant can have blanks at the beginning or end or immediately preceding an am/pm indicator.

Syntax: How to Assign Date-Time Values

In a character file

date_string [time_string]

time_string [date_string]

In a COMPUTE, DEFINE, or WHERE expression

DT(date_string [time_string])

DT(time_string [date_string])

In an IF expression

'date_string [time_string]'

'time_string [date_string]'

where:

time_string

Is a time string in acceptable format. A time string can have a blank immediately preceding an am/pm indicator.

date_string

Is a date string in numeric string, formatted-string, or translated-string format.

In an IF criteria, if the value does not contain blanks or special characters, the single quotation marks are not necessary.

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.

Example: Assigning Date-Time Literals

The DT prefix can be used in a COMPUTE, DEFINE, or WHERE expression to assign a date-time literal to a date-time field. For example:

DT2/HYYMDS = DT(20051226 05:45);                                
DT3/HYYMDS = DT(2005 DEC 26 05:45);                              
DT4/HYYMDS = DT(December 26 2005 05:45);

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

The following uses the DT function in a COMPUTE command to create a new field containing an assigned date-time value.

TABLE FILE EMPLOYEE
PRINT LAST_NAME FIRST_NAME AND COMPUTE
NEWSAL/D12.2M = CURR_SAL + (0.1 * CURR_SAL);
RAISETIME/HYYMDIA = DT(20000101 09:00AM);
WHERE CURR_JOBCODE LIKE 'B%'
END

The output is:

LAST_NAME     FIRST_NAME           NEWSAL  RAISETIME
---------     ----------           ------  ---------
SMITH         MARY             $14,520.00  2000/01/01  9:00AM
JONES         DIANE            $20,328.00  2000/01/01  9:00AM
ROMANS        ANTHONY          $23,232.00  2000/01/01  9:00AM
MCCOY         JOHN             $20,328.00  2000/01/01  9:00AM
BLACKWOOD     ROSEMARIE        $23,958.00  2000/01/01  9:00AM
MCKNIGHT      ROGER            $17,710.00  2000/01/01  9:00AM

Example: Assigning a Date-Time Value in WHERE Criteria

The following uses the DT function to create a new field containing an assigned date-time value. This value is then used as a WHERE criteria.

DEFINE FILE EMPLOYEE
NEWSAL/D12.2M = CURR_SAL + (0.1 * CURR_SAL);
RAISETIME/HYYMDIA = DT(20000101 09:00AM);
END

TABLE FILE EMPLOYEE
PRINT LAST_NAME FIRST_NAME NEWSAL RAISETIME
WHERE RAISETIME EQ DT(20000101 09:00AM)
END

The output is:

LAST_NAME     FIRST_NAME           NEWSAL  RAISETIME
---------     ----------           ------  ---------
STEVENS       ALFRED           $12,100.00  2000/01/01  9:00AM
SMITH         MARY             $14,520.00  2000/01/01  9:00AM
JONES         DIANE            $20,328.00  2000/01/01  9:00AM
SMITH         RICHARD          $10,450.00  2000/01/01  9:00AM
BANNING       JOHN             $32,670.00  2000/01/01  9:00AM
IRVING        JOAN             $29,548.20  2000/01/01  9:00AM
ROMANS        ANTHONY          $23,232.00  2000/01/01  9:00AM
MCCOY         JOHN             $20,328.00  2000/01/01  9:00AM
BLACKWOOD     ROSEMARIE        $23,958.00  2000/01/01  9:00AM
MCKNIGHT      ROGER            $17,710.00  2000/01/01  9:00AM
GREENSPAN     MARY              $9,900.00  2000/01/01  9:00AM
CROSS         BARBARA          $29,768.20  2000/01/01  9:00AM

Example: Assigning a Date-Time Value in IF Criteria

The following uses the DT function to create a new field containing an assigned date-time value. This value is then used in the IF phrase.

DEFINE FILE EMPLOYEE
NEWSAL/D12.2M = CURR_SAL + (0.1 * CURR_SAL);
RAISETIME/HYYMDIA = DT(20000101 09:00AM);
END
 
TABLE FILE EMPLOYEE
PRINT LAST_NAME FIRST_NAME NEWSAL RAISETIME
IF RAISETIME EQ '20000101 09:00AM'
END

The output is:

LAST_NAME     FIRST_NAME           NEWSAL  RAISETIME
---------     ----------           ------  ---------
STEVENS       ALFRED           $12,100.00  2000/01/01  9:00AM
SMITH         MARY             $14,520.00  2000/01/01  9:00AM
JONES         DIANE            $20,328.00  2000/01/01  9:00AM
SMITH         RICHARD          $10,450.00  2000/01/01  9:00AM
BANNING       JOHN             $32,670.00  2000/01/01  9:00AM
IRVING        JOAN             $29,548.20  2000/01/01  9:00AM
ROMANS        ANTHONY          $23,232.00  2000/01/01  9:00AM
MCCOY         JOHN             $20,328.00  2000/01/01  9:00AM
BLACKWOOD     ROSEMARIE        $23,958.00  2000/01/01  9:00AM
MCKNIGHT      ROGER            $17,710.00  2000/01/01  9:00AM
GREENSPAN     MARY              $9,900.00  2000/01/01  9:00AM
CROSS         BARBARA          $29,768.20  2000/01/01  9:00AM