convert varchar to datetime in SQL Server

Question

Have date in varchar (dayname,month date,year) like (wednesday, january 16, 1013) and i have to convert it in datetime format. i have tried options like convert function and some other. but didn't get it.

Answer 1

The conversion works without the name of the day in the string. Remove it then cast to datetime:

declare @dateString varchar(50)
set @dateString = 'wednesday, january 16, 2013'

declare @index int
set @index = charindex( ',', @dateString )
set @dateString = substring( @dateString, @index + 1, len(@dateString) - @index)

select cast(@dateString as datetime)