EXTEND()
EXTEND() operator converts its DATETIME or DATE operand to a DATETIME value of a specified (or default) precision and scale.
EXTEND() operator accepts DATE and DATETIME operands of any valid precision.
You can also use a character string as an operand for EXTEND() if this operand follows these rules:
- it cannot be a character string in the DATE format, such as "12/12/99"
- it cannot be an ambiguous numeric DATETIME value, such as "05:06" or "05" (their time units are ambiguous)
-
it cannot be a time expression that returns an INTERVAL value
EXTEND() operator doesn't accept INTERVAL operands.
DATETIME qualifiers for EXTEND()
With EXTEND(), you can use DATETIME or DATE operands of different precisions.
For this, you must specify they precision as a DATETIME qualifier:
DATETIME first TO last
If no qualifier is specified, the default qualifiers are
- YEAR TO FRACTION(3) for DATETIME operands, and
- YEAR TO DAY for DATE operands.
When you specify a DATETIME qualifier for an EXTEND() operand, you must follow these rules:
- If a first TO last qualifier is specified, the first keyword must specify a time unit that is larger than (or the same as) the time unit that is specified by the last keyword.
- If the first qualifier specifies a time unit larger than any in the operand, the missing time units are filled with values from the system clock.
-
If the last qualifier specifies a smaller time unit than any in the operand, the missing time units are assigned in this way:
- any missing MONTH or DAY is filled in with the value one (01).
- any missing HOUR, MINUTE, SECOND, or FRACTION is filled in with the value zero (00).
- If the operand contains time units outside the precision specified by the qualifier, the unspecified time units are discarded. For example, if you specify first TO last as DAY TO HOUR, any information about MONTH in the DATETIME operand will not be used in the result.
Using EXTEND() with arithmetic operators
If the precision of an INTERVAL value includes a time unit that is not present in a DATETIME or DATE value, you cannot add or subtract these two values directly: You must first use the EXTEND() operator to return an adjusted DATETIME value on which to perform the arithmetic operation.
For example, you cannot directly subtract a 720-minute INTERVAL value from the DATETIME value that has a YEAR to DAY precision. But you can perform this calculation by using EXTEND():
MAIN
DEFINE t_year_min DATETIME YEAR TO MINUTE
DISPLAY "t_year_min = EXTEND (DATETIME (2004-12-1) YEAR TO DAY, YEAR TO MINUTE)" AT 3,1
DISPLAY "- INTERVAL (720) MINUTE(3) TO MINUTE" AT 4,1
LET t_year_min = EXTEND (DATETIME (2004-12-1) YEAR TO DAY, YEAR TO MINUTE) - INTERVAL (720) MINUTE(3) TO MINUTE
DISPLAY "t_year_min = ", t_year_min AT 7,1
CALL fgl_getkey()
END MAIN
Here the EXTEND() operator returns a DATETIME value which precision is expanded from YEAR TO DAY to YEAR TO MINUTE. This adjustment allows 4gl to evaluate the arithmetic expression. The result of the subtraction has the extended precision of YEAR TO MINUTE from the first operand.
In this example, the report definition uses DATE values as operands in expressions that return DATETIME values. Output from these PRINT statements would be numeric date and time without the DATETIME keywords and qualifiers (are included here to show the precision of the values returned by the arithmetic expression):
DEFINE date_val DATE
DEFINE dt_val DATETIME YEAR TO HOUR
LET date_val = TODAY
LET dt_val = EXTEND(date_val, YEAR TO HOUR)
You cannot directly combine a DATE with an INTERVAL value for which the last qualifier is smaller than DAY. But, as in the example above, you can use the EXTEND() operator to convert DATE values to DATETIME ones that will include all the fields of the INTERVAL operand.
SQL statements can include a similar EXTEND() operator of SQL which first argument can be the name of a DATETIME or DATE database column.