SQL Convert String into DATETIME

Question

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.

Alex K. · Accepted Answer · 2015-11-09 12:15:17Z

5

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

answered Nov 9, 2015 at 12:15

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GPH Over a year ago

Clever. Didnt event think of stripping it down. Thank you!

Community · Accepted Answer · 2017-05-23 12:16:09Z

0

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.

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Nov 9, 2015 at 12:11

Yaron Idan

6,7957 gold badges47 silver badges72 bronze badges

Collectives™ on Stack Overflow

SQL Convert String into DATETIME

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related