现有SQL考勤查询需获取hh:mm格式时间差，求适用函数及修改方案

Hey there! I see you're trying to get the time spent in a hh:mm format instead of just the total integer hours from DATEDIFF(HOUR). Let's fix that up for you.

The Problem with Your Current Approach #

Right now, DATEDIFF(HOUR, ...) only returns the whole number of hours between your start and end times. If an employee worked 8 hours and 30 minutes, this would just return 8—you're missing the minute component. To get 08:30 instead, we need to calculate the total minutes between the two times, then format that into hours and minutes.

Solution 1: Using Basic String Concatenation (Works in Most SQL Server Versions) #

We'll calculate total minutes, then split that into hours and minutes, pad with leading zeros if needed, and combine them with a colon:

SELECT 
    EI.[FirstName]+' '+EI.[LastName] [EmployeeName],
    [Dpt].[FullName] [Department],
    [Desig].[FullName] [Designation],
    FirstIN = CAST(MIN([AttendanceTimeIn]) AS TIME),
    LastOUT = CAST(MAX([AttendanceTimeOut]) AS TIME),
    -- Calculate total minutes, then format as hh:mm
    HoursSpent = CONCAT(
        RIGHT('0' + CAST(DATEDIFF(MINUTE, CAST(MIN(AttendanceTimeIn) AS TIME), CAST(MAX(AttendanceTimeOut) AS TIME)) / 60 AS VARCHAR), 2),
        ':',
        RIGHT('0' + CAST(DATEDIFF(MINUTE, CAST(MIN(AttendanceTimeIn) AS TIME), CAST(MAX(AttendanceTimeOut) AS TIME)) % 60 AS VARCHAR), 2)
    ),
    CAST(COALESCE(AttendanceTimeIn, AttendanceTimeOut) AS DATE) [Date]
FROM 
    [HRM].[tbl_Designation] [Desig],
    [HRM].[tbl_Department] [Dpt],
    [HRM].[tbl_EmployeeInfo] [EI] 
    FULL OUTER JOIN [HRM].[tbl_EmployeeAttendance] [Attendance] ON [Attendance].[EmpCode] = [EI].[ID]
WHERE 
    [Dpt].[ID] = [EI].[DeptCode] 
    AND [Desig].[ID] = [EI].[DesignationCode] 
    AND [EI].[RecordStatusCode] != '13' 
    AND CAST((GETDATE()-1) AS DATE) = CAST(ISNULL([AttendanceTimeIn], [AttendanceTimeOut]) AS Date)
GROUP BY 
    EI.[FirstName]+' '+EI.[LastName],
    [Dpt].[FullName],
    [Desig].[FullName],
    CAST(COALESCE(AttendanceTimeIn, AttendanceTimeOut) AS DATE)

Solution 2: Using FORMAT (SQL Server 2012 and Later) #

If you're on a newer version of SQL Server, you can use the FORMAT function for cleaner code. We'll add the total minutes to a base date (0) and format the result as HH:mm:

SELECT 
    EI.[FirstName]+' '+EI.[LastName] [EmployeeName],
    [Dpt].[FullName] [Department],
    [Desig].[FullName] [Designation],
    FirstIN = CAST(MIN([AttendanceTimeIn]) AS TIME),
    LastOUT = CAST(MAX([AttendanceTimeOut]) AS TIME),
    -- Use FORMAT to get hh:mm directly
    HoursSpent = FORMAT(
        DATEADD(MINUTE, DATEDIFF(MINUTE, CAST(MIN(AttendanceTimeIn) AS TIME), CAST(MAX(AttendanceTimeOut) AS TIME)), 0),
        'HH:mm'
    ),
    CAST(COALESCE(AttendanceTimeIn, AttendanceTimeOut) AS DATE) [Date]
FROM 
    [HRM].[tbl_Designation] [Desig],
    [HRM].[tbl_Department] [Dpt],
    [HRM].[tbl_EmployeeInfo] [EI] 
    FULL OUTER JOIN [HRM].[tbl_EmployeeAttendance] [Attendance] ON [Attendance].[EmpCode] = [EI].[ID]
WHERE 
    [Dpt].[ID] = [EI].[DeptCode] 
    AND [Desig].[ID] = [EI].[DesignationCode] 
    AND [EI].[RecordStatusCode] != '13' 
    AND CAST((GETDATE()-1) AS DATE) = CAST(ISNULL([AttendanceTimeIn], [AttendanceTimeOut]) AS Date)
GROUP BY 
    EI.[FirstName]+' '+EI.[LastName],
    [Dpt].[FullName],
    [Desig].[FullName],
    CAST(COALESCE(AttendanceTimeIn, AttendanceTimeOut) AS DATE)

Quick Notes #

• Both solutions handle cases where minutes are less than 10 (e.g., 5 minutes becomes 05 instead of 5).
• If you have employees who work more than 24 hours (unlikely, but possible), the HH format will show the full hour count (e.g., 25:30). If you need to wrap around to days, you'd need additional logic, but that's probably not necessary here.

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