Tricky SQL-aggregate query with DateTimes

Question

I have the following DataTable (counting hours of work):

     start       |       end        |      time
-----------------+------------------+------------------
2009-05-01 08:00 | 2009-05-01 10:00 | 2009-05-01 02:00
2009-05-02 07:30 | 2009-05-02 11:00 | 2009-05-02 03:30
2009-05-03 23:00 | 2009-05-04 02:00 | 2009-05-03 03:00

There's a starttime and an endtime. The third column contains the day and the hours of work.

Is there a nice way to count the hours of work with an aggregate function? The result should be 08:30 in this case.

I am using MS SQL 2005. datetime is not valid for sum(), and the datetime-functions of MS SQL 2005 are very limited compared to MS SQL 2008.

Edit: I can't change the table-layout, and I know it is not well designed.

Answer 1

MS SQL 2005 has a DatePart function which returns an int that you can use with Sum()

Answer 2

Query on the first two columns only and use DATEDIFF() to get the hours per record. Then SUM().

Answer 3

You would have to use DATEDIFF to get the timespan covered by the two dates.

You could then create a new datetime variable set to midnight on the day in the first column and use DATEADD to add the hours from your datediff result.

Answer 4

try this:

declare @TableTime  table (StartOf datetime, EndOf datetime)
insert into @TableTime values ('2009-05-01 08:00',  '2009-05-01 10:00')
insert into @TableTime values ('2009-05-02 07:30',  '2009-05-02 11:00')
insert into @TableTime values ('2009-05-03 23:00',  '2009-05-04 02:00')


SELECT DATEDIFF(mi,StartOf,EndOf)/60.0 as Hours FROM @TableTime

SELECT SUM(DATEDIFF(mi,StartOf,EndOf)/60.0) as TotalHours FROM @TableTime

Answer 5

Assuming that the DATEDIFF option is avoided for an unspecified reason, try this:

select sum(datepart(hour,time)*60 + datepart(minute,time)) / 60.0 
  from hours_of_work

Answer 6

This will do it. Note the extra columns in the result for clarity.

declare @table table
(
    startdate datetime,
    enddate datetime
)

insert into @table
select '2009-05-01 08:00', '2009-05-01 10:00' union
select '2009-05-02 07:30', '2009-05-02 11:00' union
select '2009-05-03 23:00', '2009-05-04 02:00'

select startdate, enddate, 
DATEADD(DD, 0, DATEDIFF(DD, 0, startdate)), 
DATEDIFF(N, startdate, enddate), 
DATEADD(N, DATEDIFF(N, startdate, enddate), DATEADD(DD, 0, DATEDIFF(DD, 0, startdate)))
from @table