SQL Server 2012中字符串日期转日期字段报错求助

嘿，这个问题我之前处理过好几回，咱们先拆解下问题出在哪，再给你靠谱的解决办法：

问题原因 #

你遇到的错误本质是日期字符串解析失败，主要有两个可能的诱因：

1. 日期格式与SQL Server默认设置不匹配：你的字符串是MM/DD/YYYY（美式格式），但如果SQL Server的默认语言/日期格式是英式的DD/MM/YYYY，那像03/15/2017这种字符串会被当成“15月3日”，月份超过12自然就报“超出范围”的错误了。
2. 存在无效日期数据：你的[Date]字段里可能混了一些不符合规则的字符串，比如02/30/2017（2月没有30号）、空值或者非日期格式的乱码，这些都会导致转换失败。

解决方法 #

方法1：用CONVERT指定格式直接转换（最推荐） #

不用自己拼接字符串，CONVERT函数可以直接指定日期样式代码，101就是对应MM/DD/YYYY的美式格式，不管SQL Server的默认设置是什么，都能准确解析：

UPDATE [dbo].[Sheet5$] 
SET TDate = CONVERT(DATE, [Date], 101)

这个方法既简洁又不容易出错，优先用它。

方法2：先排查并修复无效数据 #

如果执行方法1还是报错，说明你的数据里有脏数据，先用TRY_CONVERT找出那些转不了的行：

SELECT [Date] 
FROM [dbo].[Sheet5$] 
WHERE TRY_CONVERT(DATE, [Date], 101) IS NULL

把这些行里的错误日期修正（比如把02/30/2017改成02/28/2017），或者删除无效行，之后再执行上面的UPDATE语句。

方法3：优化你原来的CAST写法（不推荐，但可以试试） #

如果你坚持要用拼接的方式，那要确保拼接后的字符串符合SQL Server能识别的通用格式，同时最好加上TRY_CAST避免单个错误数据导致整个UPDATE失败：

UPDATE [dbo].[Sheet5$] 
SET TDate = TRY_CAST(SUBSTRING([Date], 7, 4) + '-' + SUBSTRING([Date], 1, 2) + '-' + SUBSTRING([Date], 4, 2) AS DATE)

这里把斜杠改成横杠，因为YYYY-MM-DD是SQL Server通用的日期格式，不容易出错，但还是不如方法1直接用CONVERT指定样式靠谱。

内容的提问来源于stack exchange，提问作者Johnny Bones