Convert text/bad formatted date to SQL Readable DateTime

Question

I have a manually input date that looks something like this:

Wed Dec 21 19:23:32 2016 ,servername.domain.name.com,Random-String-Of-Text-Goes-Here

It's weird, I know, I can't change the way they're doing things. My goal is to get this date into any format that SQL Server sees as a date. Through a disgusting mess of code, I've managed to get it down to:

Dec 21 19:38:12 2016

substring(LEFT(FIELDVALUE, CHARINDEX(',', FIELDVALUE) - 1),5,LEN(LEFT(FIELDVALUE, CHARINDEX(',', FIELDVALUE) - 1)))

But the problem now is that the time is in between the day and year fields.

It will always be a 3Digit Month, space 2 digit day, space 8 digit time, space 4 digit year.

MON DD HH:MI:SS YYYY

Again, any format that SQL recognizes as a date will work. I'm importing these strings into a second database, and using an Excel macro or something else every time will not work. I imagine another charindex or two and I'll be there, but what I have is already such a mess I'm wondering if there is a better way to do this.

Answer 1

If I understand, your question, your looking to extract the date from the string and reformat it.

Declare @String varchar(max)='Wed Dec 21 19:23:32 2016 ,servername.domain.name.com,Random-String-Of-Text-Goes-Here'
Select Try_Convert(datetime,stuff(left(@String,charindex(' ,',@String)),1,4,'') )

Returns

2016-12-21 19:23:32.000

And with Format(), you can apply the desired format

Declare @String varchar(max)='Wed Dec 21 19:23:32 2016 ,servername.domain.name.com,Random-String-Of-Text-Goes-Here'
Select Format(Try_Convert(datetime,stuff(left(@String,charindex(' ,',@String)),1,4,'') ),'MMM dd HH:mm:ss yyyy')

Returns

Dec 21 19:23:32 2016

EDIT - With a table

Declare @YourTable table (FieldName varchar(max))
Insert Into @YourTable values
('Wed Dec 21 19:23:32 2016 ,servername.domain.name.com,Random-String-Of-Text-Goes-Here'),
('Thu Dec 22 17:05:05 2016 ,servername.domain.name.com,Random-String-Of-Text-Goes-Here-and-here')

Select AsDate    = Try_Convert(datetime,stuff(left(FieldName,charindex(' ,',FieldName)),1,4,'') )
      ,Formatted = Format(Try_Convert(datetime,stuff(left(FieldName,charindex(' ,',FieldName)),1,4,'') ),'MMM dd HH:mm:ss yyyy')
 From @YourTable

Returns

AsDate                     Formatted
2016-12-21 19:23:32.000    Dec 21 19:23:32 2016
2016-12-22 17:05:05.000    Dec 22 17:05:05 2016