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