coder net coder net - 5 months ago 22
SQL Question

standardizing differently formatted varchar field to date in sql server

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

varchar
column for a
datetime
value.

It contains dates in the following formats.

11181980
8 18 1960
10/01/1960
04-12-1953
041371
7/29/44
Empty String
NULL


When I select this column, I want to bring the date in a standard format (say
mm/dd/yyyy
) when available or NULL.

I can only think of a function to do this, but I don't want to do a UDF as I need to make sure it does not error out while trying to convert. There is no try/catch in UDF. I could do a CLR function to make use of more powerful .net features though I would like to avoid it.

Is there any other better way to handle this conversion in SQL Server? Also how should I go about doing this conversion if possible in SQL.

Answer

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 7/29/44 as 2044, not 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?

In SQL Server 2012 you will be able to use TRY_PARSE or TRY_CONVERT but with that mess of formats you're still going to have to do some massaging to get meaningful results.

Comments