Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Converting string datatype to datetime datatype

Author  Topic 

Starting Member

1 Post

Posted - 2014-07-19 : 19:09:39
I have a column which has 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 tablename
set columnname = (SUBSTRING(columnname,1,2) + '-' + SUBSTRING(columnname,3,3) + '-' +
SUBSTRING(columnname,6,4) + ' ' + SUBSTRING(columnname,11,8));

and then

alter table tablename

alter columnname datetime;

but later it shows up the error

Msg 242, Level 16, State 3, Line 1
The 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.

Premature Yak Congratulator

22864 Posts

Posted - 2014-07-20 : 13:08:17
Try this

select cast(left(columnname,9)+' '+substring(columnname,10,12) as datetime) from yourtable


Failing to plan is Planning to fail
Go to Top of Page

- Advertisement -