如何在SQL Server中快速定位CAST转换失败的无效日期数据？

SSMS内置工具操作 #

• 数据视图筛选器：

  1. 在对象资源管理器中找到表t1，右键选择「选择前1000行」打开数据视图。
  2. 在begintime列的筛选框中输入DATALENGTH(begintime) = 12（正常2023/07/10的字节长度为10，带回车空格的异常数据字节长度为12），按回车即可筛选出目标行。
  3. 若要排查所有可能的空白字符（回车、换行、空格），也可以输入LEN(RTRIM(LTRIM(begintime))) <> 10，筛选出清理前后长度不一致的行。

• 查询设计器可视化筛选：

  1. 右键表t1选择「设计」，在顶部菜单点击「查询设计器」→「新建查询」。
  2. 将begintime列添加到查询输出，在该列的「筛选」行输入DATALENGTH(begintime) <> 10，执行查询即可得到所有长度异常的数据。

直接执行SQL语句定位 #

在SSMS的查询窗口中运行以下语句，精准定位带回车/空格的无效数据：

SELECT 
    begintime,
    DATALENGTH(begintime) AS 字节长度,
    LEN(begintime) AS 字符长度
FROM t1
WHERE 
    DATALENGTH(begintime) <> 10
    OR CHARINDEX(CHAR(13), begintime) > 0 -- 检查是否包含回车
    OR CHARINDEX(CHAR(10), begintime) > 0; -- 检查是否包含换行

如果需要批量清理这些无效数据，可执行以下语句：

UPDATE t1
SET begintime = RTRIM(LTRIM(REPLACE(REPLACE(begintime, CHAR(13), ''), CHAR(10), '')))
WHERE 
    DATALENGTH(begintime) <> 10
    OR CHARINDEX(CHAR(13), begintime) > 0
    OR CHARINDEX(CHAR(10), begintime) > 0;

内容的提问来源于stack exchange，提问作者kk luo