17.00 - 17.05 - Comparison of ANSI DateTime and Interval in USING Clause - Advanced SQL Engine - Teradata Database

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

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-30
dita:mapPath
tpt1555966086716.ditamap
dita:ditavalPath
lze1555437562152.ditaval

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