如何基于DateTime2类型的上下班时间列计算平均时间？

我来帮你搞定这个计算平均上下班时间的问题，咱们先梳理下核心问题和解决方案：

核心问题分析 #

你之前的查询结果不准，主要是因为没过滤掉无效的1901-01-01记录，这些无效值转成时间后是00:00:00，会严重拉低平均值；而那些报错（比如datetime2不能直接减法、转float），是因为SQL Server不支持直接对datetime2类型做算术运算，必须先转换成可计算的数值类型。

可行解决方案 #

方案一：基于毫秒数计算平均时间 #

这个方法和你原来的思路类似，但加上了无效记录的过滤，确保只计算有效行：

SELECT 
    -- 计算平均上班时间：先转time，再算毫秒数求平均，最后转回时间格式
    CONVERT(varchar(5), 
            CAST(DATEADD(ms, AVG(DATEDIFF(ms, '00:00:00', CAST(InTime AS time))), '00:00:00') AS time),
            108) AS AVG_IN_TIME,
    -- 计算平均下班时间
    CONVERT(varchar(5), 
            CAST(DATEADD(ms, AVG(DATEDIFF(ms, '00:00:00', CAST(OutTime AS time))), '00:00:00') AS time),
            108) AS AVG_OUT_TIME
FROM [HCMSync].[dbo].[Attendances]
WHERE 
    [Email address] = 'email@domain.com' 
    AND [Date] BETWEEN '2019-01-01' AND '2019-01-08'
    -- 过滤掉无效的1901-01-01记录（datetime2要写全精度）
    AND InTime <> '1901-01-01 00:00:00.0000000'
    AND OutTime <> '1901-01-01 00:00:00.0000000'

方案二：基于总秒数计算平均时间 #

如果觉得毫秒数的转换有点绕，也可以把时间拆成时分秒转换成总秒数，求平均后再组合回时间：

SELECT 
    -- 计算平均上班时间：转成总秒数求平均，再转回时间
    CONVERT(varchar(5), 
            CAST(
                DATEADD(second, 
                        AVG(
                            DATEPART(hour, CAST(InTime AS time))*3600 + 
                            DATEPART(minute, CAST(InTime AS time))*60 + 
                            DATEPART(second, CAST(InTime AS time))
                        ), '00:00:00'
                ) AS time
            ), 108) AS AVG_IN_TIME,
    -- 计算平均下班时间
    CONVERT(varchar(5), 
            CAST(
                DATEADD(second, 
                        AVG(
                            DATEPART(hour, CAST(OutTime AS time))*3600 + 
                            DATEPART(minute, CAST(OutTime AS time))*60 + 
                            DATEPART(second, CAST(OutTime AS time))
                        ), '00:00:00'
                ) AS time
            ), 108) AS AVG_OUT_TIME
FROM [HCMSync].[dbo].[Attendances]
WHERE 
    [Email address] = 'email@domain.com' 
    AND [Date] BETWEEN '2019-01-01' AND '2019-01-08'
    AND InTime <> '1901-01-01 00:00:00.0000000'
    AND OutTime <> '1901-01-01 00:00:00.0000000'

关键注意点 #

1. 过滤无效记录：一定要加上InTime <> '1901-01-01 00:00:00.0000000'和OutTime <> ...，否则无效的00:00:00会直接拉低平均值，导致结果偏差。
2. 类型转换逻辑：SQL Server不支持直接对datetime2做减法或转float，必须先转成time类型，再用DATEDIFF或DATEPART转换成数值（毫秒/秒）进行计算，最后再转回时间格式。
3. NULL值处理：如果你的字段可能存在NULL，还要在WHERE条件里加上AND InTime IS NOT NULL和AND OutTime IS NOT NULL，避免影响平均计算。

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