15.00 - Implicit Type Conversions - Teradata Database

Implicit Type Conversions

Teradata Database permits the assignment and comparison of some types without requiring the types to be explicitly converted. Teradata Database also performs implicit type conversions in the following cases:

On some argument types passed to macros, stored procedures, and SQL functions such as SQRT.

On the expression that defines a time zone displacement in an AT clause. For details, see “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Example : Implicit Type Conversion During Assignment

Consider the following tables:

   CREATE TABLE T1

      (Fname VARCHAR(25)

      ,Fid   INTEGER

      ,Yrs   CHARACTER(2));

   CREATE TABLE T2

      (Wname VARCHAR(25)

      ,Wid   INTEGER

      ,Age   SMALLINT);

In the following statement, Teradata Database implicitly converts the character string in T1.Yrs to a numeric value:

   UPDATE T2 SET Age = T1.Yrs + 5;

This is not evident in the syntax of the source statement, but becomes evident when the dictionary information for tables T1 and T2 is accessed.

Example : Implicit Type Conversion During Comparison

Consider the table T1 in “Example 1: Implicit Type Conversion During Assignment.”

In the following statement, Teradata Database implicitly converts both operands of the comparison operation to FLOAT values before performing the comparison:

   SELECT Fname, Fid

   FROM T1

   WHERE T1.Yrs < 55;

For details on implicit type conversion of operands for comparison operations, see “Implicit Type Conversion of Comparison Operands” on page 500.

Example : Implicit Type Conversion in Parameter Passing Operations

Consider the SQRT system function that computes the square root of an argument.

In the following statement, Teradata Database implicitly converts the character argument to a FLOAT type:

   SELECT SQRT('13147688');

Supported Data Types

Teradata Database performs implicit conversion on the following types:

FROM …	TO …	For further details, see …
Byte	Byte Byte types include BYTE, VARBYTE, and BLOB.	“Byte Conversion” on page 596.
	UDT1
Numeric	Numeric	“Numeric-to-Numeric Conversion” on page 674.
	DATE	“Numeric-to-DATE Conversion” on page 669.
	Character	“Numeric-to-Character Conversion” on page 664.
	UDTa	“Numeric-to-UDT Conversion” on page 678.
DATE	Numeric	“DATE-to-Numeric Conversion” on page 641.
	DATE	“DATE-to-DATE Conversion” on page 639.
	Character	“DATE-to-Character Conversion” on page 635.
	UDTa	“DATE-to-UDT Conversion” on page 652.
Character	Numeric	“Character-to-Numeric Conversion” on page 612.
	DATE	“Character-to-DATE Conversion” on page 605.
	Character Character types include CHAR, VARCHAR, and CLOB.	“Character-to-Character Conversion” on page 600.
	Period	“Character-to-Period Conversion” on page 618.
	TIME	“Character-to-TIME Conversion” on page 621.
	TIMESTAMP	“Character-to-TIMESTAMP Conversion” on page 627.
	UDTa	“Character-to-UDT Conversion” on page 632.
TIME	UDTa	“TIME-to-UDT Conversion” on page 725.
TIMESTAMP	UDTa	“TIMESTAMP-to-UDT Conversion” on page 760.
Interval	UDTa	“INTERVAL-to-UDT Conversion” on page 662.
UDT	Predefined data types that are the target of implicit casts defined for the UDT2	“UDT-to-Character Conversion” on page 765. “UDT-to-DATE Conversion” on page 769. “UDT-to-INTERVAL Conversion” on page 772. “UDT-to-Numeric Conversion” on page 775. “UDT-to-TIME Conversion” on page 778. “UDT-to-TIMESTAMP Conversion” on page 781.
	Other UDTs that are the target of implicit casts defined for the UDTb	“UDT-to-UDT Conversion” on page 784.

---

The UDT must have an implicit cast that casts the predefined type to a UDT. To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.

To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.

For details on data types, see SQL Data Types and Literals.

Implicit Conversion of DateTime types

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 details, see “DEFAULT Phrase” in SQL Data Types and Literals.

During stored procedure execution, including the execution of the following statements: DECLARE, SELECT...INTO, and SET. See SQL Stored Procedures and Embedded SQL.

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” on page 649.
TIME	TIMESTAMP	“Implicit TIME-to-TIMESTAMP Conversion” on page 717.
TIMESTAMP	DATE	“Implicit TIMESTAMP-to-DATE Conversion” on page 734.
TIMESTAMP	TIME	“Implicit TIMESTAMP-to-TIME Conversion” on page 748.
INTERVAL	INTERVAL	“Implicit INTERVAL-to-INTERVAL Conversion” on page 658.

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” on page 649.
TIME	TIMESTAMP	“Implicit TIME-to-TIMESTAMP Conversion” on page 717.
TIMESTAMP	DATE	“Implicit TIMESTAMP-to-DATE Conversion” on page 734.
TIMESTAMP	TIME	“Implicit TIMESTAMP-to-TIME Conversion” on page 748.
Interval1	Exact Numeric	“Implicit INTERVAL-to-Numeric Conversion” on page 661.
Exact Numeric	Intervala	“Implicit Numeric-to-INTERVAL Conversion” on page 673.

---

Note: 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 “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827.

For more information, see “ANSI DateTime and Interval Data Type Assignment Rules” on page 822.

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” on page 734.
Interval1	Exact Numeric	“Implicit INTERVAL-to-Numeric Conversion” on page 661.
Exact Numeric	Intervala	“Implicit Numeric-to-INTERVAL Conversion” on page 673.

---

For more information, see “Implicit Type Conversion of Comparison Operands” on page 500.

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.

For details on data types, see SQL Data Types and Literals.

Implicit Conversion Rules

Teradata SQL performs implicit type conversions on expressions before any operation is performed.

The implementation of implicit type conversion follows the same rules as the implementation of explicit type conversion using Teradata conversion syntax. For details, see “Teradata Conversion Syntax in Explicit Data Type Conversions” on page 593.

For details on implicit type conversion of operands for comparison operations, see “Implicit Type Conversion of Comparison Operands” on page 500.

Truncation During Conversion

In some cases, implicit conversion can result in truncation of values without an error.

Recommendation: As a best practice, use an explicit CAST instead of relying on implicit conversions when possible.

Example  

Consider the following table definition:

   CREATE TABLE Test1 (c1 INT, c2 VARCHAR(1));

The following two INSERT statements complete without any errors.

   INSERT INTO Test1 VALUES (1, '1');

   INSERT INTO Test1 VALUES (2, 2);

The following query returns two rows.

   SELECT * FROM Test1;

      c1      c2

   -------------

       1       1

       2               <<<< Note that the value inserted in c2 is a blank

In the second INSERT statement, the number 2 was implicitly converted to CHAR using Teradata conversion syntax (that is, not using CAST). The process is as follows:

1 Convert the numeric value to a character string using the default or specified FORMAT for the numeric value. Leading and trailing pad characters are not trimmed.

2 Extend to the right with pad characters if required, or truncate from the right if required, to conform to the target length specification.

If non-pad characters are truncated, no string truncation error is reported.

The conversion right-justifies the number, but takes the first byte of the result which is a single blank character. For more information about numeric to character conversions, see “Numeric-to-Character Conversion” on page 664.

Restrictions

Teradata Database does not perform implicit conversion on input arguments to UDFs, UDMs, or external stored procedures (external routines). Arguments do not necessarily have to be exact matches to the parameter types, but they must be compatible. For example, you can pass a SMALLINT argument to an external routine that expects an INTEGER argument because SMALLINT and INTEGER are compatible. To pass a DATE type argument to an external routine that expects an INTEGER argument, you must explicitly cast the DATE type to an INTEGER type. For details, see SQL External Routine Programming.

Some SQL functions and operators require arguments that are exact matches to the parameter types. For details, refer to the documentation for the specific function or operator.