Using Date-Time Functions

In this section:

The functions described in this section operate on fields in date-time format (sometimes called H format).


Top of page

x
Date-Time Parameters

In this section:

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.



x
Specifying the Order of Date Components

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.



x
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


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

The WEEKFIRST parameter specifies a day of the week as the start of the week. This is used in week computations by the HDIFF, HNAME, HPART, and HSETPT functions. 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, HYYWD, and HNAME subroutines can extract a week number from a date-time value. To determine a week number, they can use ISO 8601 standard week numbering, which 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.

These functions can also define the first week of the year as the first week in January with seven days. This is the definition they used in prior releases.

You specify which type of week numbering to use by setting the WEEKFIRST parameter.

Since the week number returned by HNAME 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 from a given date-time value.



x
Syntax: How to Set a Day as the Start of the Week
SET WEEKFIRST = {value|7}

where:

value

Can be:

1 through 7, representing Sunday through Saturday with non-standard week numbering.

or

ISO1 through ISO7, representing Sunday through Saturday with ISO standard week numbering.

Note: ISO is a synonym for ISO2.

The ISO standard establishes Monday as the first day of the week, so to be fully ISO compliant, the WEEKFIRST parameter should be set to ISO or ISO2.



Example: Setting Sunday as the Start of the Week

The following designates Sunday as the start of the week:

SET WEEKFIRST = 1


x
Syntax: How to View the Current Setting of WEEKFIRST
? SET WEEKFIRST

This returns the integer value of the first day of the week. For example, the integer 1 represents Sunday.



x
Controlling 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.



x
Syntax: How to Enable Strict Processing of Date-Time Values
SET DTSTRICT = {ON|OFF}

where:

ON

Invokes strict processing. ON is the default value.

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.

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.


Top of page

x
Supplying Arguments for Date-Time Functions

Reference:

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.



x
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).



x
Using Date-Time Formats

In this section:

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.



x
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



x
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:

1999/05/20
5 20 1999
99.05.20
1999-05-20


x
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


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



x
Assigning Date-Time Values

How to:

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

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



x
Syntax: How to Assign Date-Time Values

In a character file

date_string [time_string]

or

time_string [date_string]

In a COMPUTE, DEFINE, or WHERE expression

DT(date_string [time_string])

or

DT(time_string [date_string])

In an IF expression

'date_string [time_string]'

or

'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 either 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 as an IF 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
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

WebFOCUS