Implicit Conversion of DateTime Types | Teradata Vantage - Implicit Conversion of DateTime Types - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zsn1556242031050.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantage™
Teradata Database performs implicit conversion on DateTime data types in the following cases:
  • When passing data using dynamic parameter markers, or the question mark (?) placeholder.
  • With INSERT, INSERT...SELECT, and UPDATE statements.
  • With MERGE INTO statements.
  • When handling default values for the CREATE/ALTER TABLE statements. For more information, see DEFAULT Phrase.
  • During stored procedure execution, including the execution of the following statements: DECLARE, SELECT...INTO, and SET. See Teradata Vantage™ - SQL Stored Procedures and Embedded SQL , B035-1148 .

Implicit conversion is dependent on client-side support. For information about the client products which support implicit conversion of DateTime types, see the Teradata Tools and Utilities user documentation.

The following conversions are supported.

FROM... TO... For further details, see...
DATE TIMESTAMP Implicit DATE-to-TIMESTAMP Conversion.
TIME TIMESTAMP Implicit TIME-to-TIMESTAMP Conversion.
TIMESTAMP DATE Implicit TIMESTAMP-to-DATE Conversion.
TIMESTAMP TIME Implicit TIMESTAMP-to-TIME Conversion.
INTERVAL INTERVAL Implicit INTERVAL-to-INTERVAL Conversion.

Teradata Database performs implicit conversion on DateTime data types during assignment in the following cases:

FROM... TO... For further details, see...
DATE TIMESTAMP Implicit DATE-to-TIMESTAMP Conversion.
TIME TIMESTAMP Implicit TIME-to-TIMESTAMP Conversion.
TIMESTAMP DATE Implicit TIMESTAMP-to-DATE Conversion.
TIMESTAMP TIME Implicit TIMESTAMP-to-TIME Conversion.
Interval Exact Numeric The INTERVAL type must have only one field, e.g., INTERVAL YEAR.

Implicit INTERVAL-to-NUMERIC Conversion.
Exact Numeric Interval The INTERVAL type must have only one field, e.g., INTERVAL YEAR.

Implicit Numeric-to-INTERVAL Conversion.
There is a general restriction that in Numeric-to-Interval conversions, the INTERVAL type must have only one DateTime field. However, this restriction is not an issue when implicitly converting the expression of an AT clause because the conversion is done with two CAST statements. See the date_time_expression definition in "ANSI Interval Expressions" in Teradata Vantage™ - SQL Date and Time Functions and Expressions, B035-1211.

Teradata Database performs implicit conversion on DateTime data types in single table predicates and join predicates in the following cases:

FROM... TO... For further details, see...
TIMESTAMP DATE Implicit TIMESTAMP-to-DATE Conversion.
Interval Exact Numeric The INTERVAL type must have only one field, e.g., INTERVAL YEAR.

Implicit INTERVAL-to-NUMERIC Conversion.
Exact Numeric Interval Implicit Numeric-to-INTERVAL Conversion.

For more information, see "Implicit Type Conversion of Comparison Operands" in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

The following are not supported:
  • Implicit conversion from TIME to TIMESTAMP and from TIMESTAMP to TIME are not supported in comparisons.
  • Implicit conversion of DateTime types in set operations.