Converting string datatype to datetime datatype

Please start any new threads on our new site at https://forums.sqlteam.com. 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

Vinnyvedi
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.

madhivanan
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

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources