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