CAST in Teradata. A Quick Introduction

CAST in teradata is used to convert value from one datatype into another datatype. The value must be compatible with new datatype else it will result in error. CAST can also apply different formats on output datatype. This is really helpful for date & timestamp columns.

I have used casting a lot during DATE & TIMESTAMP handling when working in Teradata.

You can also use cast to restrict number of characters in the output for string columns. This is similar to SUBSTRING function in teradata where you want first few characters only.
Example : cast(EMPLOYEE_NAME as varchar(10)).

Also CAST function is widely used while working with DECIMAL data type. It is used to restrict values to number of decimal places required in the output.

Teradata CAST Syntax

Casting in Teradata is done using "CAST" function with the new data type.
You can also specify output format if required. This is optional.

cast(column_name as new_datatype)

cast(column_name as new_datatype format 'some_format')

Teradata CAST as Decimal

You can CAST numeric values into Decimal datatype in teradata.

Decimal datatype has 2 components

Precision – This represents total number of digits
Scale – This represents total number of digits after decimal point.

So a DECIMAL value can be represented as DECIMAL(precision , scale). For example, DECIMAL(10,2) means precision of 10 and scale of 2.

Default DECIMAL value in Teradata is DECIMAL(5,0)

So if you don't specify explicitly the precision & scale Teradata will try to convert the value into decimal(5,0). This may result into numeric overflow error if the value is more than 5 digits.
CAST numeric values to decimal while handling numeric overflow errors in teradata. Read this post to know more about casting in teradata.

CAST integer as decimal

Cast integer as decimal by specifying DECIMAL(p,s) as new datatype in cast function.

select cast(10000 as decimal(10,2)) as col1;

         col1
 ------------
     10000.00

CAST numeric expression as decimal

Cast mathematical expressions output to decimal datatype as per requirement.

select cast(((19.34*27.20)+20)/9 as decimal(8,3)) as col1;

       col1
 ----------
     60.672

CAST string as decimal

You can cast varchar or char values to decimal as per requirement.

select cast('23.4672864' as decimal(10,4)) as col1;

         col1
 ------------
      23.4673

CASTING values to decimal in Teradata may result in rounding or truncation as per scale defined in the cast function.

Teradata CAST as Date

Convert string to DATE in Teradata using CAST function.

select cast('2022-04-20' as date) as col1;

     col1
 --------
 22/04/20

If you don't specify any format then the string will be converted to default DATE format. Also the string must be in standard format of 'YYYY-MM-DD' or 'YYYY/MM/DD' else you may get INVALID DATE error.

Convert string to date by specifying custom format.

select cast('2022-04-20' as date format 'yyyy-mm-dd') as col1;

      col1
----------
2022-04-20

It is absolutely must to specify proper format which is in accordance with string value of date to avoid any INVALID DATE error.

select cast('July-31-2021' as date format 'yyyy-mm-dd') as col1;

 *** Failure 2665 Invalid date.
     Statement# 1, Info =0
 *** Total elapsed time was 1 second.
 
 select cast('July-31-2021' as date format 'mmmm-dd-yyyy') as col1;

            col1
 ---------------
 July-31-2021

I will recommend below posts to you to understand Teradata CAST functions for Date in detail.