All Forums Database
mandeep_m91 5 posts Joined 05/13
08 May 2013
Is it possible to handle the date in format YYYY-MM-DD HH:MI:SS NNNNNN ? Please note there is no dot(.) in between

Hi,
This is my first post in this forum. I am new to teradata DB. There is a project that I am working on where we are receiving the date in varchar format :
YYYY-MM-DD HH:MI:SS NNNNNN 
Please note there is no dot(.) in between seconds and microseconds. I know that the timestamp(6) data type expects a dot and hence I am not able to cast it as a timestamp. One way I can think of is to manipulate the varchar string and insert a dot in between then typecast it to timestamp. I want to know if this can be achieved through casting alone ?
Thanks
Mandeep
 
 

chinna557 3 posts Joined 03/12
14 May 2013

Directly as it is you cannot use this timestamp. it will through an error 'Invalid Timestamp'.
you have to supply timestamp with dot.
sel cast (trim(cast('YYYY-MM-DD HH:MI:SS NNNNNN' as varchar(19))) || '.' ||substr(trim(cast('YYYY-MM-DD HH:MI:SS NNNNNN' as varchar(26))),21) as timestamp)
It should work.
 
 

mandeep_m91 5 posts Joined 05/13
16 May 2013

thanks! That's what I eventually ended up doing.

You must sign in to leave a comment.