BenWard (5/2/2013)
alas this table is built from daily data files going back over a number of months so getting the software house to re-export some of those csv files isn't an option.I can confirm that ALL of the data is either ymd or dmy there is no ydm or mdy.
Something like this: -
SELECT *,
CAST(
CASE WHEN PATINDEX('[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]', dates) > 0 THEN dates
WHEN PATINDEX('[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-3][0-9]', dates) > 0 THEN REPLACE(dates,'/','-')
WHEN PATINDEX('[0-3][0-9]-[0-1][0-9]-[1-2][0-9][0-9][0-9]', dates) > 0 OR
PATINDEX('[0-3][0-9]/[0-1][0-9]/[1-2][0-9][0-9][0-9]', dates) > 0 THEN SUBSTRING(dates,7,4)+'-'+SUBSTRING(dates,4,2)+'-'+SUBSTRING(dates,1,2)
ELSE NULL END AS DATETIME)
FROM (
--== SAMPLE DATA ==--
SELECT '2012-02-01'
UNION ALL SELECT '2012/03/01'
UNION ALL SELECT '01-05-1995'
UNION ALL SELECT '01/06/1995'
) a(dates);
You can make it more robust by including all of the different patterns for a valid date (e.g. for September, we'd want something like CASE WHEN PATINDEX('19[0-9][0-9]-09-30', dates) > 0 OR PATINDEX('19[0-9][0-9]-09-[1-2][0-9]', dates) > 0 OR PATINDEX('19[0-9][0-9]-09-0[1-9]', dates) > 0 OR PATINDEX('19[0-1][0-9][0-9]-09-30', dates) > 0 OR PATINDEX('19[0-9][0-9]-09-[1-2][0-9]', dates) > 0 OR PATINDEX('19[0-9][0-9]-09-0[1-9]', dates) > 0).