相加time类型字段报错Operand data type time is invalid for add operator，求解决方案

The error you're seeing happens because SQL Server doesn't allow directly adding two TIME data types using the + operator—that operator isn't supported for TIME values. Your original query tries to do Time+Total_Time, which is what's triggering the error.

Corrected Query #

Here's how to adjust your SQL to safely calculate the sum of the time values without the error:

SELECT CAST(
    DATEADD(SECOND, 
        SUM(
            DATEDIFF(SECOND, '00:00:00', [Time]) + 
            DATEDIFF(SECOND, '00:00:00', Total_Time)
        ), 
        '00:00:00'
    ) AS TIME
) AS TotalSummedTime
FROM tbl_Appointment
WHERE Total_Time = '02:00:00' AND Customer_Name = 'ju';

How This Works #

Let's break down the key changes:

• Calculate seconds for each time field: Instead of adding the TIME values directly, we convert each TIME column to its total number of seconds since midnight using DATEDIFF(SECOND, '00:00:00', [Time]) and DATEDIFF(SECOND, '00:00:00', Total_Time).
• Sum the seconds: We add these two second values per row, then sum all those totals across matching rows.
• Convert back to TIME: Finally, we use DATEADD to convert the total summed seconds back into a TIME format, then cast it explicitly to TIME.

Important Caveat #

Keep in mind that the TIME data type in SQL Server only represents a time-of-day (00:00:00 to 23:59:59.9999999). If your total summed seconds exceed 86400 (24 hours), the result will wrap around (e.g., 25 hours becomes 01:00:00). If you need to track durations longer than a full day, you'll want to calculate and display the total as hours, minutes, and seconds separately (e.g., 25:30:00) instead of using the TIME type.

Verification #

This query will:

• Filter rows where Total_Time is '02:00:00' and Customer_Name is 'ju'
• For each matching row, compute the total seconds from both Time and Total_Time
• Sum all those seconds together
• Convert the total back to a TIME value (with the wrap-around caveat noted above)

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