Convert a "UTC" datetime in character format to timestamp(0) format

Database
Enthusiast

Convert a "UTC" datetime in character format to timestamp(0) format

Hi,

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

 

Thanks!

William

 

4 REPLIES 4
Highlighted
Ambassador

Re: Convert a "UTC" datetime in character format to timestamp(0) format

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'
Enthusiast

Re: Convert a "UTC" datetime in character format to timestamp(0) format

how to convert Wed May 15 2019 16:53:00 GMT-0400 (Eastern Daylight Time) into timestamp format 'YYYY-MM-DD HH:MI:SS'?

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.

 

Ambassador

Re: Convert a "UTC" datetime in character format to timestamp(0) format

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

Teradata Employee

Re: Convert a "UTC" datetime in character format to timestamp(0) format

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?