All Forums Database
sunny.j 90 posts Joined 10/10
17 Oct 2012
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

dnoeth 4628 posts Joined 11/04
17 Oct 2012

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

Dieter

sunny.j 90 posts Joined 10/10
17 Oct 2012

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

dnoeth 4628 posts Joined 11/04
17 Oct 2012
sel cast(cast('12/25/1994 11:46:29PM'  as timestamp(0) format 'MM-DD-YYYYBhh:mi:sst' ) as time(0))

Dieter

Dieter

sunny.j 90 posts Joined 10/10
17 Oct 2012

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 .
 

sunny.j 90 posts Joined 10/10
17 Oct 2012

Any quick help please

sunny.j 90 posts Joined 10/10
17 Oct 2012

Any quick help please

 

dnoeth 4628 posts Joined 11/04
17 Oct 2012

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
 

Dieter

sunny.j 90 posts Joined 10/10
17 Oct 2012

its working thank you .
 
i thought teradata 12  will  able to convert the V2R timestamp format . 

You must sign in to leave a comment.