对于您描述的一组潜在格式:
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
as 2044
, not 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 中您将能够使用尝试解析 http://msdn.microsoft.com/en-us/library/hh213126.aspx or 尝试转换 http://msdn.microsoft.com/en-us/library/hh230993.aspx但对于这些混乱的格式,您仍然需要进行一些调整才能获得有意义的结果。