I have this datetime in character format :
Fri May 24 21:59:00 UTC 2019
I would like this in my target column as timestamp(0) format
How should I proceed ? I'm new to the converting topic in Teradata
You can either apply CAST or TO_TIMESTAMP plus a FORMAT, but both are a bit tricky.
TO_TIMESTAMP returns a Timestamp(6) and you need a double cast to strip of the frcáctional second:
Cast(Cast(To_Timestamp(col, 'dy month dd hh24:mi:ss "UTC" YYYY') AS CHAR(19)) AS TIMESTAMP(0)) -- if it's an abbreviated three char month, e.g. 'Aug' vs. 'August', switch to 'MON' instead of 'MONTH'
FORMAT chokes on the trailing Year after the time, thus you need a RegEx to rearrange the string:
Cast(RegExp_Replace(col, '...( .*)UTC (.+)', '\2 \1') AS TIMESTAMP(0) FORMAT 'yyyyBm4BddBhh:mi:ss') -- if it's an abbreviated three char month, e.g. 'Aug' vs. 'August', switch to 'm3' instead of 'm4'
This strips off the leading 'Fri' and the 'UTC' and rearranges it into 'YMD' format before CASTing:
'Fri May 24 21:59:00 UTC 2019' --> '2019 May 24 21:59:00'
I have data coming from source as Wed May 15 2019 16:53:00 GMT-0400 (Eastern Daylight Time) . In my target I have the column with datatype Timestamp(0) and in source defined as Varchar().
How to cast Wed May 15 2019 16:53:00 GMT-0400 (Eastern Daylight Time) to format 'YYYY-MM-DD HH:MI:SS' to insert into the target?
I used substr (columnname, 5,20) and result came as May 15 2019 16:53:00 but after this how do i convert into timestamp(0). can anyone please proivde the syntax for this?
I am using T16 version.
Please create a new topic for new questions.
This should work:
Cast(Cast(To_Timestamp(col, 'dy MON dd yyyy hh24:mi:ss "GMT"TZHTZM') AS CHAR(19)) AS TIMESTAMP(0))
Again, might need MONTH instead of MON
Or possibly CAST(SUBSTR(columnname,5,20) AS TIMESTAMP(0) FORMAT 'M3BDDBYYYYBHH:MI:SS')
For months other than May, is the month a 3-letter abbreviation or is it the full name?
Do you need to adjust for the time zone (or multiple time zones) in the input?