15.10 - Data Conversion - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

You can use data definition phrases in expressions to convert data to another type or modify data attributes.

When used to modify the attributes of returned values, the data description phrase immediately follows the column being modified.

where:

 

Syntax element …

Specifies …

expression

the data expression to be converted to the new definition defined by data_type_list

data_type_list

a data type declaration or data attributes or both.

List elements must be separated by commas.

Note: This syntax is called Teradata conversion syntax, and is non-ANSI. Using Teradata conversion syntax is strongly discouraged. It is a Teradata extension to the ANSI SQL:2011 standard and is retained only for backward compatibility with existing applications. You should always use the CAST function to perform conversions in new applications to ensure ANSI compatibility.

For example, in the following SELECT statement, a TITLE phrase overrides the default heading (the column name) for EmpNo, and a FORMAT phrase modifies the display format defined for Salary data in the CREATE TABLE statement.

   SELECT EmpNo (TITLE 'Emp#'), Salary (FORMAT '$$$,$$9.99')
   FROM Employee;

Restrictions apply to the data types that Teradata conversion syntax supports. For example, you cannot use Teradata conversion syntax to convert data to a UDT. For further details, see SQL Functions, Operators, Expressions, and Predicates.

For applications that need to conform to ANSI as well as to modify attributes of a value, use the CAST function instead of Teradata conversion syntax.

To select Name as a fixed length field and Salary as an integer value:

   SELECT CAST (Name AS CHAR(12)), CAST (Salary AS INTEGER)
   FROM Employee ;

For information on Teradata conversion syntax and the CAST function, see SQL Functions, Operators, Expressions, and Predicates.