Calculate difference between dates in SQL using datetime fields without converting to char/varchar

Question

I'm currently looking at finding time differences between dates and outputting to hours (00:00). However, I'm then going on to sum this calculation which means I can't convert to char/varchar.

Please see example data below how I would like it to appear.

EMPLOYEE_ID Start               End                 Duration
1           05/06/2015 08:30    05/06/2015 12:12    03:42
1           05/06/2015 13:18    05/06/2015 17:00    03:42
1           06/06/2015 08:30    06/06/2015 12:12    03:42
1           06/06/2015 13:18    06/06/2015 17:00    03:42

Current code for Duration:

CONVERT(varchar(5),DATEADD(minute,DATEDIFF(minute,Start, End),0), 114) as Duration

Obviously, this code is not how I want it as it's varchar and I'd like to go on to sum two durations together. I have looked around but can't find anything which shows how this would be done.

Answer 1

Calculate the duration in minutes:

select datediff(minute, start, end) as duration_min
. . .

This is much easier to work with. Then sum the minutes:

select sum(datediff(minute, start, end)) as duration_min

I would recommend leaving it like this. But if you want it in HH:MM format, then manually convert to a string of the format you want.

Don't use a time data type. It is limited to 24 hours.

Answer 2

SQL Server doesn't currently supoort the ANSI SQL INTERVAL data type so about the best you can do to support aggregation is calculate the duration in a consistent unit of measure (like minutes you used here) and then use the aggregation result to calculate the interval from a fixed datetime value. Although the example below uses the FORMAT function to convert the result to a string, I suggest formatting for display purposes be done in the presentation layer rather than T-SQL.

CREATE TABLE dbo.EmployTimeRecord (
      EMPLOYEE_ID int NOT NULL
    , StartTime smalldatetime NOT NULL
    , EndTime smalldatetime NOT NULL
    , DurationMinutes AS DATEDIFF(minute, StartTime, EndTime)
    );

INSERT INTO dbo.EmployTimeRecord(EMPLOYEE_ID, StartTime, EndTime)
    VALUES
      (1, '2015-05-06T08:30:00', '2015-05-06T12:12:00')
    , (1, '2015-05-06T13:18:00', '2015-05-06T17:00:00')
    , (1, '2015-05-06T08:30:00', '2015-05-06T12:12:00')
    , (1, '2015-05-06T13:18:00', '2015-05-06T17:00:00');


SELECT
      EMPLOYEE_ID
    , FORMAT(DATEADD(minute, SUM(DurationMinutes), ''), 'HH:mm') AS Duration
FROM dbo.EmployTimeRecord
GROUP BY EMPLOYEE_ID;