Converting varchar to timestamp

General

Converting varchar to timestamp

All I have a table that stores the timestamp in this format hhmmss000000 so for example it the field might have 142750261904 this equates to 14:27:50.261904.

How can I write a query to convert that from a varchar(25) to a timestamp?

I have tried:

sel cast( '142750261904' as timestamp(6) format 'HH:MI:SS.S(6)')

but get an invalid timestamp messsage. Thanks.

Dan

2 REPLIES
Enthusiast

Re: Converting varchar to timestamp

you can try this:

select cast(regexp_replace( '142750261904',

'([[:digit:]]{2})([[:digit:]]{2})([[:digit:]]{2})

([[:digit:]]{6})', '\1:\2:\3.\4')

as time format 'hh:mi:ss.s(6)')

If things get complicated , mostly I write  udfs in java, c, c++ because it is easier 

Teradata Employee

Re: Converting varchar to timestamp

TIMESTAMP contains both DATE and TIME portions. Your example appears to be a TIME.

Teradata will accept format HHMISS with no separator characters, but wants some separator between whole and fractional seconds.