taT4Sql | Changing Timestamp Formats using Teradata SQL

While working on some project at work, I was making use of Teradata SQL for querying databases and loading some table by creating various volatile tables and doing numerous joins in-between them.

Few days back, I came to know  from the client side that, I have to pull some data from a table’s attribute VC having datatype , VARCHAR(N) and insert into another table’s attribute TS, whose datatype would be TIMESTAMP(6). That’s an issue, if data is not in right format and hence, the client stated the following..

So, data present in VC having format ‘YYYYMMDDHHMISS’ and I have to change it to format ‘YYYY-MM-DD HH:MI:SS’, so as to get the data in right format for TS.

Trick 2 Change Format..

INSERT INTO
     SOME_TS_TBL(TS)
SELECT
     SUBSTR(VC,1,4) || '-' || SUBSTR(VC,5,2) || '-' || SUBSTR(VC,7,2) || '  ' ||
     SUBSTR(VC,9,2) || ':' || SUBSTR(VC,11,2) || ':' || SUBSTR(VC,13,2)
FROM
     SOME_VC_TBL

Assuming, SOME_TS_TBL has only one attribute (TS) and SOME_VC_TBL has VC. This computation will do the trick and transformation.

HTH..

Advertisements

One thought on “taT4Sql | Changing Timestamp Formats using Teradata SQL

  1. Pingback: taT4Sql | Comparing different Date formats using Teradata SQL « PrayogShala

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s