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.