15.00 - Comparison of ANSI DateTime and Interval in USING Clause - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)

Comparison of ANSI DateTime and Interval in USING Clause

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):

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