Is it possible to handle the date in format YYYY-MM-DD HH:MI:SS NNNNNN ? Please note there is no dot(.) in between

Database
Enthusiast

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

2 REPLIES

Re: Is it possible to handle the date in format YYYY-MM-DD HH:MI:SS NNNNNN ? Please note there is no dot(.) in between

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.

Enthusiast

Re: Is it possible to handle the date in format YYYY-MM-DD HH:MI:SS NNNNNN ? Please note there is no dot(.) in between

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