How to cast the String to Date in Teradata?

Posted on by

CAST function in Teradata

CAST function is used to converts an expression of a given data type to a different data type in Teradata. If the given expression is not valid for the targeted data type, it will throw the error.

Syntax of CAST function in Teradata

  • expression – an expression with known data type to be cast as a different data type.
  • sql_data_type – the new data type for expression.

Example : CAST the String to DATE

In some cases, we want to explicitly mention the Date value and compare the same with some of Date column in Teradata table. If we didn’t convert the String value to Date value, Teradata will not provide the correct result set.

To resolve this issue,We can use CAST function to convert the String data type to Date.

Lets execute this function against the table which contains Date columns. Here is the Student table with the Date column of Dateofbirth.

Cast as Date in Teradata
Cast as Date in Teradata

Using the CAST function, we have converted the string value of ‘2019-12-01’ to Date value and extracting the required row from the Student table.

Cast as Date example in Teradata
Cast as Date example in Teradata

Lets use the Interval function to subtract the 1 month from the given Date value without CAST function.

Since we didn’t convert the String data type to Date, Interval function is not able to subtract month from 2019-12-01 and it throws the error as below.

Subtract month from String value without Cast
Subtract month from String value without Cast

If we apply the CAST function in this place, this error will be resolved and we will get the required output.

Now the Cast function converted the String to Date type and the interval function has subtracted the one month from the Date value.

Cast function to subtract the value from DATE
Cast function to subtract the value from DATE

Recommended Articles