Teradata Type Conversion Functions and Examples

Teradata is one of the common and widely used MPP database. Just like many relational databases, Teradata supports many useful functions. You can use these functions to covert the value of one data type to another. In this article, will check commonly used Teradata type conversion functions with some examples.

The type conversion functions use common calling function i.e. the first argument is the value to be formatted or converted, and the second argument is a template that defines the output or input format. These conversion functions should be used within your Teradata SQL queries. In short, you cannot run these functions without SELECT clause.

Teradata Type Conversion Functions

Before jumping into type conversion functions, let us see how to check the data type an expression or column value in Teradata.

Teradata TYPE Function

Similar to python, Teradata supports type function to verify the data type of an expression or column value.

For examples, consider below example to demonstrate typeof function to identify data type of an expression or value.

consider below example to identify the data type of ‘string’, you can use below syntax.

SELECT type('123') as d_type;

d_type
VARCHAR(3) CHARACTER SET UNICODE

SELECT type('string') as d_type;

d_type
VARCHAR(3) CHARACTER SET UNICODE

SELECT type(123) as d_type;

d_type
BYTEINT

Following are some of commonly used Teradata type conversion functions and examples on usage. Note that, in this article, we have explained commonly used Teradata type conversion functions.

Teradata CAST Function

The CAST function is one of the commonly used data type conversion functions. The CAST function allows you to perform run-time conversions between compatible data types. Syntax and the usage are similar to the CAST function of the other database.

Following is the syntax:

CAST ( expression AS type );

Teradata CAST Function Examples

The CAST function will convert the type of a table column or an expression to another compatible data type.

For example, consider the examples on usage of CAST function:

 select cast('123456' as INT) as col1;
  col1 
 123456

The result be the converted value. However, function will return null if it fails to cast type.

Teradata TO_NUMBER Function

The TO_NUMBER function is one of the easiest function that you can use to convert string containing integer value to integer type.

Following is the syntax of TO_NUMBER function available in Teradata.

TO_NUMBER( stringType );

Teradata TO_NUMBER Function Examples

As mentioned, TO_NUMBER converts string or character filed containing integer or number values.

For examples, consider below examples to demonstrate usage to convert string to number type.

select TO_NUMBER('123345') as col1;
col1
123345

The Result of the function will be number type. However, result will be null if input is not a number representation.

Teradata TO_CHAR Function

Teradata TO_CHAR function Converts the specified value to a character string. The value could be a date, timestamp, real, double precision, or numeric values or expressions.

The TO_CHAR function is one of very useful when you are working with miscellaneous data sets.

Following is the syntax of TO_CHAR Function.

TO_CHAR (timestamp_expression | numeric_expression , 'format');

Teradata TO_CHAR Function Examples

The TO_CHAR function is used in various applications.

For examples, consider below examples to demonstrate usage.

select to_char(date '2019-06-02', 'YYYY Month') as dm; 
dm
2019 June

select to_char(timestamp '2019-06-02 20:19:07', 'Day, DD HH:MI:SS am') as dm;
dm
Sunday   , 02 08:19:07 PM

Teradata TO_DATE Function

TO_DATE converts a date represented in a character string to a DATE data type.

Following is the syntax.

TO_DATE (timestamp_expression | numeric_expression , 'format');

Teradata TO_DATE Function Examples

The TO_DATE function will work on date represented in a character string.

For example, below example converts string representation to date format.

select to_date ('01 Jan 2000', 'DD Mon YYYY') as dt;
dt
1/1/2000

Teradata TO_TIMESTAMP Function

The TO_TIMESTAMP function converts string_expr or integer_expr to a TIMESTAMP data type.

Following is the syntax of to_timestamp function available in Teradata.

TO_TIMESTAMP (timestamp_expression | numeric_expression , 'format');

Teradata TO_TIMESTAMP Function Examples

For example, consider below example to demonstrates to_timestamp function in Teradata.

select TO_TIMESTAMP ('01 Jan 2000', 'DD Mon YYYY') as dt;

dt
1/1/2000 00:00:00.000000