Other than DATE values in INTEGERDATE mode, external values for DateTime and Interval data are expressed as fixed length CharFix character strings (in logical characters) in the designated client character set for the session.
The type provided in a USING request modifier for any client data to be used as a DateTime value can be defined either as the appropriate DateTime type or as CHARACTER(n), where n is a character string the correct length for the external form of a DateTime or Interval data type, and the character string is to be used internally to represent a DateTime or Interval value.
When a client creates USING data without being aware of the ANSI DateTime data types, those fields are typed as CHARACTER(n). Then when those USING values appear in the assignment lists for INSERTs or UPDATEs, the field names from the USING phrase can be used directly.
An example follows:
USING (TimeVal CHARACTER(11), NumVal INTEGER, TextVal (CHARACTER(5)) INSERT INTO TABLE_1 (:TimeVal, :NumVal, :TextVal);
When you import ANSI DateTime values with a USING request modifier and the values are to be used for actions other than an INSERT or UPDATE, you must explicitly CAST them from the external character format to the proper ANSI DateTime type.
An example follows:
USING (TimeVal CHARACTER(11), NumVal INTEGER) UPDATE TABLE_1 SET TimeField=:TimeVal, NumField=:NumVal WHERE CAST(:TimeVal AS TIME(2)) > TimeField;
While you can use TimeVal CHARACTER(11) directly for assignment in this USING request modifier, you must CAST the column data definition explicitly as TIME(2) in order to compare the field value TimeField in the table because TimeField is an ANSI TIME defined as TIME(2).
You can use both DateTime and Interval declarations to allow a USING request modifier to directly indicate that an external character string value is to be treated as a DateTime or Interval value. To import such values, you import their character strings directly into the USING request modifier.
If you move values into a USING request modifier and the character string data cannot be converted into valid internal DateTime or Interval values as indicated by their type definitions, then the system returns an error to the application.