在SQL Server中标准化迄今为止格式不同的varchar字段

admin

在SQL Server中标准化迄今为止格式不同的varchar字段

sql

我知道过去曾问过类似的问题，但仍然没有为我的案件提供适当的解决方案。

我有一个数据库表（第三方），其中有varchar一个datetime值列。

它包含以下格式的日期。

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

当我选择此列时，我希望将日期以标准格式（例如mm/dd/yyyy）提供（如果可用）或NULL。

我只能想到一个函数来执行此操作，但是我不想执行UDF，因为我需要确保在尝试转换时不会出错。UDF中没有try /
catch。我可以做一个CLR函数来利用更强大的.net功能，尽管我想避免这样做。

还有其他更好的方法来处理SQL Server中的此转换吗？如果可能的话，我还应该如何进行这种转换。

阅读 129

2021-07-01

共1个答案

admin

对于可能的格式集，您已经描述了：

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;

根据服务器设置，这将解释7/29/44为2044，而不是1944。为了确保所有日期都是过去的日期，您可以执行以下操作：

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;

这还取决于没有垃圾数据，不能将其压缩到一个日期中。什么样的系统进入这种不一致的废话呢？

在SQL Server 2012中，您将能够使用TRY_PARSE或TRY_CONVERT，但由于格式混乱，您仍然必须进行一些按摩才能获得有意义的结果。

2021-07-01