In this section: How to: |
A date-time expression returns date and time components. You can create these expressions using a variety of supplied date-time functions. For details about date-time functions, see the Using Functions manual.
SET DATEFORMAT = option
where:
For an example, see Specifying the Order of Date Components for a Date-Time Field.
An external date-time value is a constant in character format from one of the following sources:
A date-time constant or a date-time value as it appears in a character file has one of the following formats:
time_string [date_string] date_string [time_string]
A date-time constant in a COMPUTE, DEFINE, or WHERE expression must have one of the following formats:
DT(time_string [date_string]) DT(date_string [time_string])
A date-time constant in an IF expression has one of the following formats:
'time_string [date_string]' 'date_string [time_string]'
If the value contains no blanks or special characters, the single quotation marks are not necessary. Note that the DT prefix is not supported in IF criteria.
where:
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 the second can be expressed with a decimal point or be followed by a colon:
If a numeric-string format longer than eight digits is encountered, it is treated as a combined date-time string in the Hn format. The following are examples of numeric string date constants:
99 1999 19990201
1999/05/20 5 20 1999 99.05.20 1999-05-20
January 6 2000
Note:
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 request sets DATEFORMAT to MYD:
SET DATEFORMAT = MYD DEFINE FILE EMPLOYEE DTFLDYYMD/HYYMDI = DT(APR 04 05); END TABLE FILE EMPLOYEE PRINT CURR_SAL DTFLDYYMD END
The output shows that the natural date literal 'APR 04 05' is interpreted as April 5, 1904:
CURR_SAL DTFLDYYMD -------- --------- $11,000.00 1904/04/05 00:00 $13,200.00 1904/04/05 00:00 $18,480.00 1904/04/05 00:00 $9,500.00 1904/04/05 00:00 $29,700.00 1904/04/05 00:00 $26,862.00 1904/04/05 00:00 $21,120.00 1904/04/05 00:00 $18,480.00 1904/04/05 00:00 $21,780.00 1904/04/05 00:00 $16,100.00 1904/04/05 00:00 $9,000.00 1904/04/05 00:00 $27,062.00 1904/04/05 00:00
The DTTRANS comma-delimited transaction file has an ID field and a date-time field that contains both the date (as eight characters) and time (in the format hour:minute:second):
01, 20000101 02:57:25,$ 02, 19991231 14:05:35,$
Because the transaction file contains the dates in numeric string format, the DATEFORMAT setting is not used, and the dates are entered in YMD order.
The following transaction file is also valid. It contains formatted string dates that comply with the default DATEFORMAT setting, MDY:
01, 01/01/2000 02:57:25,$ 02, 12/31/1999 14:05:35,$
The following Master File describes the FOCUS data source named DATETIME, which receives these values:
FILE=DATETIME, SUFFIX=FOC ,$ SEGNAME=DATETIME, SEGTYPE=S0 ,$ FIELD=ID, ID, USAGE = I2 ,$ FIELD=DT1, DT1, USAGE=HYYMDS ,$
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
In a WHERE clause, a date-time constant must use the DT( ) format:
TABLE FILE VIDEOTR2 PRINT CUSTID TRANSDATE WHERE TRANSDATE GT DT(2000/01/01 02:57:25) END
The output is:
CUSTID TRANSDATE ------ --------- 1118 2000/06/26 05:45 1237 2000/02/05 03:30
In an IF clause, a date-time constant must be enclosed in single quotation marks if it contains any blanks:
TABLE FILE VIDEOTR2 PRINT CUSTID TRANSDATE IF TRANSDATE GT '2000/01/01 02:57:25' END
Note: The DT prefix for a date-time constant is not supported in an IF clause.
The output is:
CUSTID TRANSDATE ------ --------- 1118 2000/06/26 05:45 1237 2000/02/05 03:30
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.
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.
Computations only allow direct assignment within data types: alpha to alpha, numeric to numeric, date to date, and date-time to date-time. All other operations are accomplished through a set of date-time functions.
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 the 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, EDIT, and LIKE on date-time operands are not supported. Prefix operators that work with alphanumeric fields are supported.
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
Consider the following request using the VIDEOTR2 data source:
TABLE FILE VIDEOTR2 PRINT CUSTID TRANSDATE AND COMPUTE DT2/HYYMDH = TRANSDATE; T1/HHIS = TRANSDATE; WHERE DATE EQ 2000 END
The output is:
CUSTID TRANSDATE DT2 T1 ------ --------- --- -- 1118 2000/06/26 05:45 2000/06/26 05 05:45:00 1237 2000/02/05 03:30 2000/02/05 03 03:30:00
WebFOCUS |