Converting date string to SQL DATETIME - hopefully without a udf
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.