SQL Server中如何计算VARCHAR类型时间列的开放时长差？

表：Museum_hours #

字段说明：open和close的类型均为VARCHAR。

问题 #

如何在SQL Server中通过这两个字段的差值得到open_hours（开放时长）？

解决方案 #

由于open和close是字符串类型，需先转换为TIME类型再计算时间差，以下是几种实用实现方式：

1. 输出「X小时Y分钟」格式的时长

SELECT 
    Museum_id,
    day,
    open,
    close,
    CONCAT(
        DATEDIFF(MINUTE, CAST(open AS TIME), CAST(close AS TIME)) / 60,
        '小时',
        DATEDIFF(MINUTE, CAST(open AS TIME), CAST(close AS TIME)) % 60,
        '分钟'
    ) AS open_hours
FROM Museum_hours;

2. 输出以小时为单位的小数结果

SELECT 
    Museum_id,
    day,
    open,
    close,
    DATEDIFF(MINUTE, CAST(open AS TIME), CAST(close AS TIME)) / 60.0 AS open_hours
FROM Museum_hours;

3. 兼容无效时间格式的容错写法

如果存在格式不规范的时间字符串，可使用TRY_CAST避免查询报错：

SELECT 
    Museum_id,
    day,
    open,
    close,
    CASE 
        WHEN TRY_CAST(open AS TIME) IS NOT NULL AND TRY_CAST(close AS TIME) IS NOT NULL
        THEN CONCAT(
            DATEDIFF(MINUTE, TRY_CAST(open AS TIME), TRY_CAST(close AS TIME)) / 60,
            '小时',
            DATEDIFF(MINUTE, TRY_CAST(open AS TIME), TRY_CAST(close AS TIME)) % 60,
            '分钟'
        )
        ELSE '无效时间格式'
    END AS open_hours
FROM Museum_hours;

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