Conversion of string/text to datetime.

[vinnyvedi]

I have a column which has more than 10,000 rows with string in the form of 05MAY2006:04:34:00.000000 it is stored as varchar(25). I need to save it as datetime in the same column. I have tried using update tablenameset columnname = (SUBSTRING(columnname,1,2) + '-' + SUBSTRING(columnname,3,3) + '-' + SUBSTRING(columnname,6,4) + ' ' + SUBSTRING(columnname,11,8));and then alter table tablenamealter columnname datetime;but later it shows up the errorMsg 242, Level 16, State 3, Line 1The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.How do I change it any other opinion or any modification for the above query. Please help. Thank you.

[niche]

If that string is a fixed length, use MID and CONCAT to put it into any format you need.

Edited July 20, 2014 by niche