In this section: |
When using standard date and time functions, you need to understand the settings that alter the behavior of these functions, as well as the acceptable formats and how to supply values in these formats.
You can affect the behavior of date and time functions in the following ways:
These are the standard date and time functions:
DATEADD: Adding or Subtracting a Date Unit to or From a Date
DATECVT: Converting the Format of a Date
DATEDIF: Finding the Difference Between Two Dates
DATEMOV: Moving a Date to a Significant Point
DATETRAN: Formatting Dates in International Formats
HADD: Incrementing a Date-Time Value
HCNVRT: Converting a Date-Time Value to Alphanumeric Format
HDATE: Converting the Date Portion of a Date-Time Value to a Date Format
HDIFF: Finding the Number of Units Between Two Date-Time Values
HDTTM: Converting a Date Value to a Date-Time Value
HEXTR: Extracting Components of a Date-Time Value and Setting Remaining Components to Zero
HGETC: Storing the Current Date and Time in a Date-Time Field
HHMMSS: Retrieving the Current Time
HINPUT: Converting an Alphanumeric String to a Date-Time Value
HMIDNT: Setting the Time Portion of a Date-Time Value to Midnight
HMASK: Extracting Components of a Date-Time Field and Preserving Remaining Components
HNAME: Retrieving a Date-Time Component in Alphanumeric Format
HPART: Retrieving a Date-Time Component in Numeric Format
HSETPT: Inserting a Component Into a Date-Time Value
HTIME: Converting the Time Portion of a Date-Time Value to a Number
HYYWD: Returning the Year and Week Number From a Date-Time Value
TIMETOTS: Converting a Time to a Timestamp
TODAY: Returning the Current Date
In this section: |
You can determine which days are work days and which are not. Work days affect the DATEADD, DATEDIF, and DATEMOV functions. You identify work days as business days or holidays. You can also specify which day is the start of the week. This is used in week computations by the HDIFF, HNAME, HPART, and HSETPT functions.
Business days are traditionally Monday through Friday, but not every business has this schedule. For example, if your company does business on Sunday, Tuesday, Wednesday, Friday, and Saturday, you can tailor business day units to reflect that schedule.
SET BUSDAYS = smtwtfs
where:
Is the seven character list of days that represents your business week. The list has a position for each day from Sunday to Saturday:
If a letter is not in its correct position, or if you replace a letter with a character other than an underscore, you receive an error message.
The following designates work days as Sunday, Tuesday, Wednesday, Friday, and Saturday:
SET BUSDAYS = S_TW_FS
You can specify a list of dates that are designated as holidays in your company. These dates are excluded when using functions that perform calculations based on working days. For example, if Thursday in a given week is designated as a holiday, the next working day after Wednesday is Friday.
To define a list of holidays, you must:
In Windows and UNIX, the file must be HDAYxxxx.ERR
In z/OS, the file must be a member of ERRORS named HDAYxxxx.
where:
Is a string of text four characters long.
SET HDAY = xxxx
where:
Is the part of the name of the holiday file after HDAY. This string must be four characters long.
The following is the HDAYTEST file, which establishes holidays:
19910325 TEST HOLIDAY 19911225 CHRISTMAS
This request uses HDAYTEST in its calculations:
SET BUSDAYS = SMTWTFS SET HDAY = TEST TABLE FILE MOVIES PRINT TITLE RELDATE COMPUTE NEXTDATE/YMD = DATEADD(RELDATE, 'BD', 1); WHERE RELDATE GE '19910101'; END
How to: |
If you use a date and time function in Dialogue Manager that returns a numeric integer format, Dialogue Manager truncates any leading zeros. For example, if a function returns the value 000101 (indicating January 1, 2000), Dialogue Manager truncates the leading zeros, producing 101, an incorrect date. To avoid this problem, use the LEADZERO parameter.
LEADZERO only supports an expression that makes a direct call to a function. An expression that has nesting or another mathematical function always truncates leading zeros. For example,
-SET &OUT = AYM(&IN, 1, 'I4')/100;
truncates leading zeros regardless of the LEADZERO parameter setting.
SET LEADZERO = {ON|OFF}
where:
Displays leading zeros if present.
Truncates leading zeros. OFF is the default value.
The AYM function adds one month to the input date of December 1999:
-SET &IN = '9912'; -RUN -SET &OUT = AYM(&IN, 1, 'I4'); -TYPE &OUT
Using the default LEADZERO setting, this yields:
1
This represents the date January 2000 incorrectly. Setting the LEADZERO parameter in the request as follows:
SET LEADZERO = ON -SET &IN = '9912'; -SET &OUT = AYM(&IN, 1, 'I4'); -TYPE &OUT
results in the following:
0001
This correctly indicates January 2000.
In this section: How to: |
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.
SET DATEFORMAT = option
where:
Can be one of the following: MDY, DMY, YMD, or MYD. MDY is the default value for the U.S. English format.
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
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.
The following are examples of numeric string date constants:
String |
Date |
---|---|
99 |
January 1, 1999 |
1999 |
January 1, 1999 |
19990201 |
February 1, 1999 |
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.
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
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.
The following date is in translated-string format:
January 6 2000
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.
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
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.
In this section: How to: Reference: |
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.
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:
Is a time string in acceptable format. A time string can have a blank immediately preceding an am/pm indicator.
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.
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);
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
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
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
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.
SET WEEKFIRST = {value|7}
where:
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.
The following designates Sunday as the start of the week:
SET WEEKFIRST = 1
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 |
|
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:
SET DTSTRICT = {ON|OFF}
where:
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).
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.
WebFOCUS |