15.00 - Teradata Conversion Syntax in Explicit Data Type Conversions - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

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,