SQL Server 2012中忽略CAST转换错误计算通话时长差值

针对你遇到的格式错误数据导致CAST报错的问题，我给你几个实用的解决方案，都是适配SQL Server 2012版本的：

方案1：用TRY_CONVERT直接转换为时间类型（推荐，适合时间≤24小时的场景） #

如果你的CallLength和CallLengthActual都是合法的hh:mm:ss格式（且小时数不超过23），可以直接用TRY_CONVERT把字符串转成time类型，再用DATEDIFF计算秒数差。这个方法最简洁，而且TRY_CONVERT在转换失败时会返回NULL，不会触发报错：

DATEDIFF(SECOND, TRY_CONVERT(time, [CallLengthActual]), TRY_CONVERT(time, [CallLength])) AS Variance

如果其中某个字段格式错误，对应的TRY_CONVERT会返回NULL，最终Variance也会是NULL，不会中断整个查询。要是你希望错误数据返回默认值（比如0），可以套个ISNULL：

ISNULL(DATEDIFF(SECOND, TRY_CONVERT(time, [CallLengthActual]), TRY_CONVERT(time, [CallLength])), 0) AS Variance

方案2：拆分时间段单独用TRY_CAST处理（支持超过24小时的场景） #

如果你的数据里有超过24小时的时长（比如25:30:00），time类型没法存储，那就得拆分小时、分钟、秒分别转换，每个转换步骤用TRY_CAST兜底：

(
    ISNULL(TRY_CAST(SUBSTRING([CallLength], 1, CHARINDEX(':', [CallLength]) - 1) AS INT), 0) * 3600
    + ISNULL(TRY_CAST(SUBSTRING([CallLength], CHARINDEX(':', [CallLength]) + 1, 2) AS INT), 0) * 60
    + ISNULL(TRY_CAST(RIGHT([CallLength], 2) AS INT), 0)
)
-
(
    ISNULL(TRY_CAST(SUBSTRING([CallLengthActual], 1, CHARINDEX(':', [CallLengthActual]) - 1) AS INT), 0) * 3600
    + ISNULL(TRY_CAST(SUBSTRING([CallLengthActual], CHARINDEX(':', [CallLengthActual]) + 1, 2) AS INT), 0) * 60
    + ISNULL(TRY_CAST(RIGHT([CallLengthActual], 2) AS INT), 0)
) AS Variance

这里要注意：你原来的SUBSTRING用了起始位置0，SQL Server里SUBSTRING的起始位置是从1开始的，虽然0会被自动视为1，但改成1更规范，避免后续混淆。

方案3：先验证格式再计算（精准过滤错误数据） #

要是你想先严格筛选出格式正确的数据再计算，可以用LIKE匹配hh:mm:ss的格式，再用CASE分支处理：

CASE 
    WHEN [CallLength] LIKE '[0-9]%:[0-9][0-9]:[0-9][0-9]' 
         AND [CallLengthActual] LIKE '[0-9]%:[0-9][0-9]:[0-9][0-9]'
    THEN 
        (
            CAST(SUBSTRING([CallLength], 1, CHARINDEX(':', [CallLength]) - 1) AS INT) * 3600
            + CAST(SUBSTRING([CallLength], CHARINDEX(':', [CallLength]) + 1, 2) AS INT) * 60
            + CAST(RIGHT([CallLength], 2) AS INT)
        )
        -
        (
            CAST(SUBSTRING([CallLengthActual], 1, CHARINDEX(':', [CallLengthActual]) - 1) AS INT) * 3600
            + CAST(SUBSTRING([CallLengthActual], CHARINDEX(':', [CallLengthActual]) + 1, 2) AS INT) * 60
            + CAST(RIGHT([CallLengthActual], 2) AS INT)
        )
    ELSE NULL -- 这里可以改成你想要的默认值，比如0
END AS Variance

这个LIKE表达式能匹配1位或2位小时的情况（比如9:05:03或09:05:03），如果你的数据格式更固定（比如必须是2位小时），可以把[0-9]%改成[0-9][0-9]。

另外你提到TRY_CAST似乎不工作，大概率是因为你原来的写法是把整个计算结果一次性CAST，而中间的子表达式（比如SUBSTRING得到的非数字值）在计算时就已经报错了，不是最后CAST的问题。把每个需要转换的部分单独用TRY_CAST就能解决这个问题。

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