I know similar questions have been asked in the past, but they still haven't given me a proper solution for my case.
I have a database table (third party) that has a
8 18 1960
For the set of potential formats you've described:
DECLARE @x TABLE(y VARCHAR(32)) INSERT @x VALUES ('11181980'), ('8 18 1960'), ('10/01/1960'), ('04-12-1953'), ('041371'), ('7/29/44'), (''), (NULL); SET DATEFORMAT MDY; SELECT CONVERT(DATETIME, CASE WHEN y LIKE '%/%' THEN y WHEN LEN(RTRIM(y)) = 0 THEN NULL WHEN LEN(RTRIM(y)) IN (6,8) AND ISNUMERIC(y) = 1 THEN STUFF(STUFF(y,3,0,'/'),6,0,'/') END) FROM (SELECT y = REPLACE(REPLACE(y, ' ', '/'), '-', '/') FROM @x) AS x;
This will interpret
1944, based on server settings. To make sure all dates are in the past, you could do:
SELECT y = DATEADD(YEAR, CASE WHEN y > GETDATE() THEN -100 ELSE 0 END, y) FROM ( SELECT y = CONVERT(DATETIME, CASE WHEN y LIKE '%/%' THEN y WHEN LEN(RTRIM(y)) = 0 THEN NULL ELSE STUFF(STUFF(y, 3, 0, '/'),6, 0, '/') END) FROM (SELECT y = REPLACE(REPLACE(y, ' ', '/'), '-', '/') FROM @x) AS x ) AS z;
This also depends on there being no garbage data that can't be massaged into a date. What kind of system enters this kind of inconsistent nonsense anyway?