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
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' )
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 .
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')