Teradata Conversion Syntax in Explicit Data Type Conversions
Teradata conversion syntax is defined as follows:
Syntax
where:
|
Syntax element … |
Specifies … |
|
expression |
the data expression to be converted to the new definition specified by data_type and data_attributes. |
|
data_type |
a data type declaration such as INTEGER or DATE. |
|
data_attribute |
a data attribute such as FORMAT or TITLE. |
ANSI Compliance
This is a Teradata extension to the ANSI SQL:2011 standard.
Using CAST Instead of Teradata Conversion Syntax
Using Teradata conversion syntax is strongly discouraged. It is an extension to the ANSI SQL:2011 standard and is retained only for backward compatibility with existing applications. Instead, use CAST to explicitly convert data types.
Usage Notes
When the conversion specifies data_type, then the data is converted at run time. At that time, a data conversion or range check error may occur.
For any kind of data type conversion using Teradata conversion syntax, where the item that includes a data type declaration is an operand of a complex expression, you must either enclose the appropriate entities in parentheses or use the CAST syntax.
You should always use the CAST function to perform conversions in new applications to ensure ANSI compatibility.
Related Topics
For further rules that apply to the conversion between specific data types, for example, numeric-to numeric or character-to-numeric, see the appropriate succeeding topic in this chapter.
Example
To evaluate an expression of the following form correctly:
column_name (INTEGER) + variable
You could enter the expression as follows:
(column_name (INTEGER)) + variable
or, preferably, as:
CAST (column_name AS INTEGER) + variable
For more information on using CAST, see “CAST in Explicit Data Type Conversions” on page 590.
Example
Here is an example that uses the Teradata conversion syntax, and specifies the FORMAT data attribute to convert the format of a DATE data type.
CREATE TABLE date1 (d1 DATE FORMAT 'E4,BM4BDD,BY4');
CREATE TABLE char1 (c1 CHAR(10));
INSERT date1 ('Saturday, March 16, 2002');
INSERT INTO char1 (c1)
SELECT ((d1 (FORMAT 'YYYY/MM/DD')))
FROM date1;
SELECT * FROM char1;
The result from the SELECT statement is:
c1
----------
2002/03/16
If the second INSERT statement did not convert the DATE format to 'YYYY/MM/DD', the result from the SELECT statement is:
c1
----------
Saturday,