SQL Server中NVARCHAR格式时长转decimal及转换报错问题咨询

转换逻辑与代码实现 #

核心思路是把WorkedHours按冒号分割为小时、分钟两部分，小时部分直接取数值，分钟部分除以60换算为小时小数，两者相加即可得到目标结果：

基础版（仅适配合规格式数据） #

如果确认表中所有WorkedHours都严格符合HH:MM格式，直接使用以下查询：

SELECT
    WorkedHours,
    -- 可根据需求调整DECIMAL的精度位数
    CAST(LEFT(WorkedHours, CHARINDEX(':', WorkedHours) - 1) AS DECIMAL(4,2))
    + CAST(RIGHT(WorkedHours, LEN(WorkedHours) - CHARINDEX(':', WorkedHours)) AS DECIMAL(4,2)) / 60
    AS WorkedHoursDecimal
FROM 你的表名

兼容异常值版（避免非法格式报错） #

如果存在空值、格式不符合要求的数据，使用TRY_CAST和条件判断兼容异常场景，非法格式会返回NULL，可自行修改为默认值：

SELECT
    WorkedHours,
    CASE
        WHEN WorkedHours LIKE '%:%'
            AND ISNUMERIC(LEFT(WorkedHours, CHARINDEX(':', WorkedHours) -1)) = 1
            AND ISNUMERIC(RIGHT(WorkedHours, LEN(WorkedHours) - CHARINDEX(':', WorkedHours))) = 1
        THEN
            TRY_CAST(LEFT(WorkedHours, CHARINDEX(':', WorkedHours) - 1) AS DECIMAL(4,2))
            + TRY_CAST(RIGHT(WorkedHours, LEN(WorkedHours) - CHARINDEX(':', WorkedHours)) AS DECIMAL(4,2)) / 60
        ELSE NULL -- 可替换为0或其他你需要的默认值
    END AS WorkedHoursDecimal
FROM 你的表名

「从值为0的字符转换为int类型报错」原因说明 #

该报错通常由以下两种场景触发：

• 分割字符串逻辑异常拿到空值：当WorkedHours存在不符合HH:MM格式的内容（比如:30、9:、空字符串、单个字符0等），没有判断冒号存在就执行分割逻辑会得到空字符串，SQL Server中将空字符串强转为INT时会隐式尝试映射为数值0，嵌套运算场景下就会触发该转换报错。
• 错误的时间类型转INT逻辑：如果你采用了先把WorkedHours转为TIME类型、再直接强转TIME为INT的方案，TIME类型的底层存储结构和INT不兼容，当值为00:00:00（时长为0）时，直接转换操作就会抛出该错误。

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