Varchar to Timestamp(6) conversion

Database
Teradata Employee

Varchar to Timestamp(6) conversion

My customer has loaded a TD test table with a date & time column that looks like this:

Column:      XXXXXX   varchar(23) 

Data:

7/31/2014 9\:20\:03 AM

8/4/2014 8\:23\:15 AM

12/6/2014 2\:31\:07 PM 

What is the quickest/easiest way to convert the data to TIMESTAMP(6),  if possible at all ?   

The output should look something like    YYYY-MM-DD HH:MM:SS.ssssss   

Thanks

Orlando

1 REPLY
N/A

Re: Varchar to Timestamp(6) conversion

Hi Orlando,

what's your TD release?

In TD14 you might utilize Oracle's TO_TIMESTAMP, which is a bit more flexilbe regarding single digit day/hour/minute/second, but month still needs to be two digits. But quoted characters like "\" seem to be allowed only in TO_CHAR.

So add the missing zero, remove the backslash and pass it to TO_TIMESTAMP:

TO_TIMESTAMP(OREPLACE(CASE WHEN x LIKE '_/%' THEN '0' ELSE '' END || x, '\',''), 'mm/dd/yyyy hh:mi:ss AM')