SQL中DATETIME类型时间差按HH:MM格式输出的实现问询

Got it, let's solve this! When you need to get the time difference between two DATETIME values formatted as HH:MM—including total hours that span multiple days, like your example of 28:23—here's a straightforward, reliable approach for SQL Server:

Step-by-Step Solution #

The key is to calculate the total number of minutes between the two dates first, then break that down into hours and minutes, ensuring the minutes are always two digits.

DECLARE @D1 DATETIME = '2019-11-18 06:00:00', @D2 DATETIME = '2019-11-19 10:23:00';

-- Calculate total minutes between the two dates (use ABS to handle reverse order)
DECLARE @TotalMinutes INT = ABS(DATEDIFF(MINUTE, @D1, @D2));

-- Format into HH:MM string
SELECT 
    CONCAT(
        FLOOR(@TotalMinutes / 60),  -- Total hours (truncates decimal part)
        ':', 
        RIGHT('0' + CAST(@TotalMinutes % 60 AS VARCHAR(2)), 2)  -- Pad minutes to two digits
    ) AS FormattedTimeDifference;

How It Works #

• DATEDIFF(MINUTE, @D1, @D2) gets the exact total minutes between the two dates. Using ABS ensures you get a positive value even if @D2 is earlier than @D1.
• FLOOR(@TotalMinutes / 60) converts total minutes into full hours (since dividing integers in SQL Server truncates decimals, FLOOR is optional here but makes the intent clearer).
• RIGHT('0' + CAST(@TotalMinutes % 60 AS VARCHAR(2)), 2) ensures minutes are always displayed as two digits (e.g., 3 becomes "03" instead of "3").

Example Output #

For your sample dates, this query will return:

28:23

If you prefer a single-line solution without variables, you can wrap the logic into one SELECT statement:

DECLARE @D1 DATETIME = '2019-11-18 06:00:00', @D2 DATETIME = '2019-11-19 10:23:00';

SELECT 
    CONCAT(
        FLOOR(ABS(DATEDIFF(MINUTE, @D1, @D2)) / 60),
        ':',
        RIGHT('0' + CAST(ABS(DATEDIFF(MINUTE, @D1, @D2)) % 60 AS VARCHAR(2)), 2)
    ) AS FormattedTimeDifference;

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