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:
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 |
|
DATE |
||
Character |
||
UDTa |
||
DATE
|
Numeric |
|
DATE |
||
Character |
||
UDTa |
||
Character
|
Numeric |
|
DATE |
||
Character Character types include CHAR, VARCHAR, and CLOB. |
||
Period |
||
TIME |
||
TIMESTAMP |
||
UDTa |
||
TIME |
UDTa |
|
TIMESTAMP |
UDTa |
|
Interval |
UDTa |
|
UDT
|
Predefined data types that are the target of implicit casts defined for the UDT2 |
|
Other UDTs that are the target of implicit casts defined for the UDTb |
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:
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 |
|
TIME |
TIMESTAMP |
|
TIMESTAMP |
DATE |
|
TIMESTAMP |
TIME |
|
INTERVAL |
INTERVAL |
Teradata Database performs implicit conversion on DateTime data types during assignment in the following cases:
FROM... |
TO... |
For further details, see... |
DATE |
TIMESTAMP |
|
TIME |
TIMESTAMP |
|
TIMESTAMP |
DATE |
|
TIMESTAMP |
TIME |
|
Interval1 |
Exact Numeric |
|
Exact Numeric |
Intervala |
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 |
|
Interval1 |
Exact Numeric |
|
Exact Numeric |
Intervala |
For more information, see “Implicit Type Conversion of Comparison Operands” on page 500.
The following are not supported:
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.