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 ?
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.