vinnyvedi Posted July 20, 2014 Share Posted July 20, 2014 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. Link to comment Share on other sites More sharing options...
niche Posted July 20, 2014 Share Posted July 20, 2014 (edited) 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now