16.20 - Comparison of ANSI DateTime and Interval in USING Clause - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

External values for ANSI DateTime and Interval data are expressed as fixed length character strings in the designated client character set for the session.

When you import ANSI DateTime and Interval values with a USING phrase, you must explicitly cast them from the external character format to the proper ANSI DateTime and Interval types for comparison.

For example, consider the following statement, where the data type of the TimeField column is TIME(2):

   USING (TimeVal CHARACTER(11), NumVal INTEGER)
   UPDATE TABLE_1
   SET TimeField=:TimeVal, NumField=:NumVal
   WHERE CAST(:TimeVal AS TIME(2)) > TimeField;

Although you can use TimeVal CHAR(11) directly for assignment in this USING phrase, 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).