Casting character data to timestamp or date data type is a common task during ETL development. Yet, I couldn’t learn the formatting characters by heart 🙂 When CASTing, one must tell the Teradata database what the source format is for it being able to build the target data type. The source format specification is done using the FORMAT keyword succeeded by a parameter comprising of formatting characters. These may be found in the Teradata’s reference manual:
Examples:
/* YYYY-MM-DD format */
SELECT CAST('2017-09-30' AS DATE FORMAT 'YYYY-MM-DD') AS output;
/* YYYY-MM-DD HH:MI:SS format, B stands for space between the date and time part */
SELECT CAST('2017-09-30 22:19:10' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SS') AS output;
/* YYYYMMDDHHMISS format */
SELECT CAST('20170930221910' AS TIMESTAMP(0) FORMAT 'YYYYMMDDHHMISS') AS output;
/* YYYYMMDD format */
SELECT CAST('20170930' AS DATE FORMAT 'YYYYMMDD') AS output;
/* YYYY-MM-DD HH:MI:SS:ssssss format to TIMESTAMP(6) */
SELECT CAST('2017-09-30 22:19:10.123456' AS TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)') AS output;
/* YYYY-MM-DD HH:MI:SS:ssssss format to TIMESTAMP(0), there's no direct way of reducing timestamp's precisiou */
SELECT CAST(SUBSTRING('2017-09-30 22:19:10.123456' FROM 0 FOR 20) AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SS') AS output;Happy casting!
Leave a Reply