Nice, Luis. I didn't consider using regular expression. I went down the road of splitting the parts of the date and then concatenating them and converting the result into a datetime.

CREATE TABLE dbo.bad_dates (

record_created varchar(32));

INSERT INTO dbo.bad_dates(record_created)

VALUES('21 Dec 2014 00:16:15'),

('25 Oct 2014 00:16:18'),

('30 Dec 2014 00:16:53'),

('90000'),

('23 Oct 2013 00:16:27');

WITH cteSplit AS (

SELECT d.record_created,

MAX(CASE WHEN s.itemnumber = 2 THEN s.item END) month_part,

MAX(CASE WHEN s.itemnumber = 1 THEN s.item END) day_part,

MAX(CASE WHEN s.itemnumber = 3 THEN s.item END) year_part,

MAX(CASE WHEN s.itemnumber = 4 THEN s.item END) time_part

FROM dbo.bad_dates d

CROSS APPLY DelimitedSplit8K(d.record_created, ' ') s

GROUP BY d.record_created

)

SELECT CONVERT(datetime, month_part + ' ' + day_part + ', ' + year_part + ' ' + time_part)

FROM cteSplit

WHERE month_part IS NOT NULL;

Please note that Luis's approach does the filtering by using regular expressions, so the bad data is eliminated up front.