c# - 在 sql server 中标准化不同格式的 varchar 字段

Question

我知道过去有人问过类似的问题，但他们仍然没有为我的案例提供合适的解决方案。

我有一个数据库表(第三方)，它有一个 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 中有没有其他更好的方法来处理这种转换？另外，如果可能的话，我应该如何在 SQL 中进行这种转换。

Answer 1

对于您描述的一组潜在格式:

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但是对于那些困惑的格式，您仍然需要进行一些修改才能获得有意义的结果。