EXTEND Function

The EXTEND function adjusts the precision of a DATETIME or DATE value.

The DATETIME or DATE expression that is its first argument cannot be a quoted string representation of a DATE value.

If you do not specify first and last qualifiers, the default qualifiers are YEAR TO FRACTION(3).

If the expression contains fields that are not specified by the time-unit qualifiers, those fields are discarded.

If the first qualifier specifies a larger (that is, more significant) time unit than what exists in the expression, the new fields are filled in with values returned by the CURRENT function. If the last qualifier specifies a smaller time unit (that is, less significant) than what exists in the expression, the new fields are filled in with constant values. A missing MONTH or DAY field is filled in with 1, and the missing HOUR to FRACTION fields are filled in with 0.

In the following expression, the EXTEND call returns the call_dtime column value with YEAR TO SECOND precision:

EXTEND (call_dtime, YEAR TO SECOND)

You can use the EXTEND function to perform addition or subtraction with a DATETIME value and an INTERVAL value that do not have the same time unit qualifiers. The next expression expands a literal DATETIME YEAR TO DAY value to a precision of YEAR TO MINUTE so that an interval YEAR TO MINUTE value can be subtracted from it:

EXTEND (DATETIME (2009-8-1) YEAR TO DAY, YEAR TO MINUTE)
   - INTERVAL (720) MINUTE (3) TO MINUTE

You can use the EXTEND function to selectively update a subset of the time units in a DATETIME value. The UPDATE statement in the next example updates only the hour and minute time unit values in a DATETIME YEAR TO MINUTE column.

UPDATE cust_calls SET call_dtime = call_dtime -
   (EXTEND(call_dtime, HOUR TO MINUTE) - DATETIME (11:00) 
   HOUR TO MINUTE) WHERE customer_num = 106;

Subtracting 11:00 from the DATETIME HOUR TO MINUTE value returned by EXTEND yields a positive or negative INTERVAL HOUR TO MINUTE value. Subtracting this difference from the original value in the call_dtime column forces the updated hour and minute time unit values to 11:00 in the cust_calls.call_dtime column.