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 TIBCO FOCUS® Describing Data manual.

Reference: Automatic Conversion Between Date and Date-Time Formats

In early releases of date-time fields, you were required to use date-time functions for all conversions between date and date-time formats. While these functions are still supported for conversions, the requirement to use them has been eliminated in certain operations.

The following automatic direct operations are supported between date and date-time formats:

  • Assignment.

    Assignment of a date field or a date constant to a date-time field. The time component is set to zero (midnight). The date can be a full component date such as YYMD or a partial component date such as YYM. It cannot be a single component date such as Q, as this type of date, although displayed as a date in reports, is stored as an integer value and is used as an integer value in expressions.

    Assignment of a date-time field or date-time constant to a date field. The time components are removed.

  • Comparison and Subtraction.

    When a date-time value is compared with or subtracted from a date value, or a date value is compared with or subtracted from a date-time value, the date is converted to date-time with the time component set to midnight. They are then compared or subtracted as date-time values.

  • Function parameters.

    Simplified date functions can use either date or date-time values as their date parameters. Legacy user functions do not support this new functionality. The date-time functions (H functions) use date-time parameters and the new date functions use new dates, which are stored as offsets from a base date.

Recognition and use of date or date-time constants.

  • Constants can be expressed as strings, without the DT operator.
  • Constants are converted to or from date or date-time values in accordance with the field format they are compared with, subtracted from, or assigned to.
  • Unless it is expressed in a non-ambiguous translated or formatted string format with proper delimiters (not as a numeric string or number), the recognition of a constant as a date depends on the format of its field counterpart.

    In this case, the size in terms of number of digits is strictly limited to at least six for a full component date or date-time value, (eight for a four-digit year), three for a partial component date, and one for a single component date.

  • When numeric constants are used as function parameters and, therefore, do not have a field counterpart, they are recognized according to YYMD or YMD format. The only exception is a string with a single blank or the number zero which, in reports, will be presented as a blank. Date offset constants are no longer allowed. Blank separators between digits in a string are also not supported.

For additional information about date and date-time formats, see the Describing Data With TIBCO WebFOCUS® Language manual.

Example: Assigning Date and Date-Time Values

The following request generates a date-time value using the DT_CURRENT_DATETIME function. It then assigns this value to a date field and assigns that date field to a date-time field.

TABLE FILE WF_RETAIL_LITE
PRINT QUANTITY_SOLD NOPRINT AND COMPUTE
 DATETIME1/HYYMDm =  DT_CURRENT_DATETIME(MILLISECOND);  
     AS 'Date-Time 1'
COMPUTE
 DATE1/YYMD   = DATETIME1;
     AS 'Date'
COMPUTE
DATETIME2/HYYMDm = DATE1;
     AS 'Date-Time 2'
WHERE RECORDLIMIT EQ 20
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image. The original date-time field has a non-zero time component. When assigned to the date field, the time component is removed. When that date is assigned to the second date-time field, a zero time component is added.

Example: Comparing Date and Date-Time Values

The following request creates one date-time field and one date field. When QUANTITY_SOLD is 1, they have the same date value and the date-time field has a zero time component. When QUANTITY_SOLD is 2, they have different date values, and the date-time field has a zero time component. In all other cases, the date-time field has the current date with a non-zero time component, and the date field has the current date. The EQUAL1 field compares them to see if they compare as equal.

TABLE FILE WF_RETAIL_LITE
PRINT QUANTITY_SOLD AS Quantity AND COMPUTE
 DATETIME1/HYYMDm = IF QUANTITY_SOLD EQ 1 THEN '2017/06/05'
        ELSE IF QUANTITY_SOLD EQ 2 THEN '2016/02/29'
        ELSE DT_CURRENT_DATETIME(MILLISECOND);  
        AS 'Date-Time'
COMPUTE
  DATE1/YYMD   = IF QUANTITY_SOLD EQ 1 THEN '2017/06/05'
        ELSE IF QUANTITY_SOLD EQ 2 THEN '2015/12/30'     
        ELSE DT_CURRENT_DATE(); 
        AS 'Date'
COMPUTE
  EQUAL1/A1 = IF DATETIME1 EQ DATE1 THEN 'Y' ELSE 'N';
        AS 'Equal?'
WHERE RECORDLIMIT EQ 12
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image. When a date value is compared to a date-time value, the date is converted to a date-time value with the time component set to zero, and then the values are compared. Therefore, when QUANTITY_SOLD is 1, both the date components are equal and the time component of the date-time field is set to zero, so when the date is converted to a date-time value, they are equal. When QUANTITY_SOLD is 2, the date components are different, so they are not equal. When QUANTITY_SOLD is 3, the date components are the same, but the date-time field has a non-zero time component. Therefore, when the date field is converted to a date-time value with a zero time component and they are compared, they are not equal.

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.

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 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 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, although it is no longer required, 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.

Manipulating Date-Time Values

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