如何查找选择转换失败值

Question

我有一个来自核心数据的查询，它是nvarchar，所有值都是'00：00：00'格式。我想把它转换成长。当我尝试转换前 1000 名时，它工作正常，但所有值都有问题。查询显示在下面

SELECT DATEDIFF(second, '00:00', CAST(TimeSpent AS time(7)))* cast(1000 as bigint) + RIGHT(CAST(TimeSpent AS time(7)),7) FROM [mtr].[MatterDocument]

错误语句为

从字符串转换日期和/或时间时转换失败

如何找到转换失败的值？

Answer 1

我建议你的MatterDocument表中有一些不好的数据。 SQL Server 不支持正则表达式搜索，但幸运的是，它的LIKE运算符确实支持一些我们可以使用的原始正则表达式：

SELECT *
FROM [mtr].[MatterDocument]
WHERE TimeSpent NOT LIKE '[01][0-9]:[0-5][0-9]:[0-5][0-9]' AND
TimeSpent NOT LIKE '2[0-3]:[0-5][0-9]:[0-5][0-9]';

演示

您可以在演示中验证是否正在清除错误的、不可接受的时间字符串。 上面的查询也应该可以清除可能根本不是时间值的字符串，并以某种方式将其放入您的表中。

最好的长期解决方法是在源上更正数据，然后将数据作为真正的日期/时间类型引入 SQL Server。

编辑：TRY_CAST，正如@Denis在他的回答中所描述的那样，可能是另一种方法。 但这需要 SQL Server 2012 或更高版本。 上述查询在早期版本中应该仍然有效。

Answer 2

尝试使用TRY_CAST函数查找错误的行(如果无法转换值，则返回NULL(

SELECT c.TimeSpent, /*Any columns to identify rows */
FROM (
SELECT TimeSpent, /*Any columns to identify rows */ 
DATEDIFF(second, '00:00', TRY_CAST(TimeSpent AS time(7)))* cast(1000 as bigint)
+ RIGHT(TRY_CAST(TimeSpent AS time(7)),7) AS Converted
FROM [mtr].[MatterDocument]
) c
WHERE Converted IS NULL

Answer 3

您应该找到错误的值：

select timespent
from t
where try_cast(TimeSpent AS time(7)) is null;

这将使您能够找到错误的值。 它们可能是超过 23 的倍。

我建议更简单地进行转换：

select (left(TimeSpent, 2) * 60 * 60 + 
substring(TimeStpent, 4, 2) * 60 +
right(TimeSpent, 2)
) as seconds

这将在没有 SQL Servertime数据类型限制的情况下进行转换。