Converting date string to SQL DATETIME - hopefully without a udf I am taking an Excel file that has dates in different formats but one of these formats is giving me some grief when I try converting it or casting it to a SQL DATETIME. For example: 

Thu Oct 16 13:40:19 2014 ,Tue Apr  1 12:20:40 2014, etc.

Essentially the format is ddd MM dd HH:mm:ss yyyy. 

I was wondering if there's some kind of date style that would allow me to do a convert in one line. I could write a function for this but I was wondering if there was a more elegant solution. I Googled with no luck.
This works:

DECLARE @mycol VARCHAR(30) = 'Tue Apr  1 12:20:40 2014'

SELECT CAST(SUBSTRING(@mycol, PATINDEX('% %', @mycol), 100) AS DATETIME)


The day name at the beginning is what prevents it from being able to convert directly.

Instead of declaring the variable, you can just substitute the column containing your dates for @mycol in the script above.