Creating a Date-Time Expression

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.


Top of page

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.

For an example, see Specifying the Order of Date Components for a Date-Time Field.


Top of page

x
Specifying a Date-Time Value

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:

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 the second can be expressed with a decimal point or be followed by a colon:

  • If there is a colon after the second, the value following it represents the millisecond. There is no way to express the microsecond or nanosecond using this notation.
  • A decimal point in the second value indicates the decimal fraction of a second. A microsecond can be represented using six decimal digits. A nanosecond can be represented using nine 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 and eight-digit strings contain two or four digits for the year, followed by two for the month, and then two for the day. Because the component order is fixed with this format, the DATEFORMAT setting described in How to Specify the Order of Date Components in a Date-Time Field is ignored.

    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
  • Formatted-string format contains a one or two-digit day, a one or two-digit month, and a two or four-digit year separated by spaces, slashes, hyphens, or periods. All three parts must be present and follow the DATEFORMAT setting described in How to Specify the Order of Date Components in a Date-Time Field. 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 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:



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: Specifying the Order of Date Components for a Date-Time Field

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


Example: Reading Date-Time Values From a Transaction File

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 ,$


Example: Using a Date-Time Value in a COMPUTE Command
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: Using a Date-Time Value in WHERE Criteria

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


Example: Using a Date-Time Value in IF Criteria

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


Example: Specifying 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.


Top of page

x
Manipulating Date-Time Values

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.



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


Example: Assigning a Different Usage Format to a Date-Time Column

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