16.20 - Example - Teradata Vantage NewSQL Engine

Teradata Vantage™ Data Types and Literals

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

Field TS1 in the table INTTIMESTAMP is a TIMESTAMP value with the explicit format ' Y4-MM-DDBHH:MI:SSDS(6) '. Assume that you want to convert this to a value of CHAR(19), and an explicit output format of ' M3BDD,BY4BHHhMIm '.

SELECT TS1 FROM INTTIMESTAMP;

The result (without a type change) is the following report:

                       TS1
--------------------------
1900-12-31 08:25:37.899231

Now use nested CAST phrases and a FORMAT to obtain the desired result: a report in character format.

SELECT
CAST( (CAST (TS1 AS FORMAT 'M3BDD,BY4BHHhMIm'))
AS CHAR(19))
FROM INTTIMESTAMP;

The result after the nested CASTs is the following report.

TS1
-------------------
Dec 31, 1900 08h25m

The inner CAST establishes the display format for the TIMESTAMP value and the outer CAST indicates the data type of the desired result.