Jirka's Public Notepad

Data Engineering | Python | SQL Server | Teradata

September 22, 2017 By Jiří Hubáček Leave a Comment

Casting Char to Timestamp and Date in Teradata

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:

  • Date format reference
  • Time format reference



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!

Related

Filed Under: Teradata Tagged With: don't remember, Teradata SQL

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • GitHub
  • LinkedIn
  • RSS
  • Twitter
© 2021 · Jiří Hubáček, PGP