The dateform mode of the session is INTEGERDATE and column F1 in the table INTDAT is a DATE value with the explicit format ' YYYY,MMM,DD '.
SELECT F1 FROM INTDAT ;
The result (without a type change) is the following report:
F1 ---------- 1900,Dec,31
Assume that you want to convert this to a value of CHAR(12), and an explicit output format of ' MMMBDD,BYYYY '. Use nested CAST phrases and a FORMAT to get the desired result: a report in character format.
SELECT CAST( (CAST (F1 AS FORMAT 'MMMBDD,BYYYY')) AS CHAR(12)) FROM INTDAT;
The result after the nested CASTs is the following report.
F1 ------------ Dec 31, 1900
The inner CAST establishes the display format for the DATE value and the outer CAST indicates the data type of the desired result.