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.