All Forums General
dlayten 3 posts Joined 01/13
31 Oct 2014
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

Raja_KT 1246 posts Joined 07/09
31 Oct 2014

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 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Fred 1096 posts Joined 08/04
03 Nov 2014

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.
 

You must sign in to leave a comment.