Example: TIMESTAMP-to-Character Conversion - Teradata VantageCloud Lake

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 get 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.