CODEWITHSUNDEEP
sql-server-2008t-sql
GPH
GPH

Reputation: 1897

SQL Convert String into DATETIME

I need to (if its possible) convert a column that stores the date as a string in the following format Monday October 19, 2015 to an usable datetime for example 01/10/2015

Every cast and convert I have tried to run it through just returns

Conversion failed when converting date and/or time from character string.

Nothing I can do about the saved format as its part of a fixed process.

Upvotes: 1

Views: 161

Answers (2)

Alex K.
Alex K.

Reputation: 175748

Chop off the day:

DECLARE @S VARCHAR(32) = 'Monday October 19, 2015'

SELECT CAST(SUBSTRING(@S, CHARINDEX(' ', @S) + 1, LEN(@S)) AS DATETIME)

> 2015-10-19 00:00:00.000

Upvotes: 5

Yaron Idan
Yaron Idan

Reputation: 6765

You should manipulate the string to this format - "DD/MON/YYYY", where DD is day, MON is the first three letters of the months name, and YYYY is the year. You could do that easily do that using string functions, and from there you can insert the date into the DB using conventional conversions, such as the ones listed in this post - Convert DD-Mon-YYYY to DD/MM/YYYY.

Upvotes: 0

Related Questions