varchar to timestamp conversion

Database
Enthusiast

varchar to timestamp conversion

Hi

i have a column with date and time in varchar , im trying to convert it to timestamp .

sel cast(''12/25/1994 11:46:29PM'  as timestamp(0) format 'DD-MM-YYYYhh:mi:sst' )

But it showing me an error invalid time .

any quick help on the same

8 REPLIES
Junior Contributor

Re: varchar to timestamp conversion

In your example day and month are exchanged and you forgot the blank.

This should work:

sel cast('12/25/1994 11:46:29PM'  as timestamp(0) format 'MM-DD-YYYYBhh:mi:sst' )

Dieter

Enthusiast

Re: varchar to timestamp conversion

i want to extract time from the data and time attribute in the format of  'HH:MI:SS' , but Teradata showing me invalid timestamp

Junior Contributor

Re: varchar to timestamp conversion

sel cast(cast('12/25/1994 11:46:29PM'  as timestamp(0) format 'MM-DD-YYYYBhh:mi:sst' ) as time(0))

Dieter

Enthusiast

Re: varchar to timestamp conversion

thanks its working .

if my timestamp will be in the format  '20-SEP-12 02.27.45.145000 PM' then the given query is failing with error message invalid time .

Enthusiast

Re: varchar to timestamp conversion

Any quick help please

Enthusiast

Re: varchar to timestamp conversion

Any quick help please

Junior Contributor

Re: varchar to timestamp conversion

Of course this is failing, it's a totally different format.

In each new post there's a different scenario.

What do you expect?

Teradata automagically casting any kind of timestamp string?

Me reading your mind?

Sorry, but my crystal ball is out of order today.

This is my last try:

CAST(SUBSTRING(x FROM POSITION (' ' IN x) + 1 FOR 8) || SUBSTRING(c FROM CHAR_LENGTH(x)-1) AS TIME(0) FORMAT 'hh:mi:sst')

Dieter

Enthusiast

Re: varchar to timestamp conversion

its working thank you .

i thought teradata 12  will  able to convert the V2R timestamp format .