how to sum total hours from date-time field in sql

I am trying to sum total hours that someone was sleeping from date-time field.  For example,  if a person sleeps at 1:00 am and wakes up on 5:00 am then i want to show that person slept for 4 hours as total hours of sleep from 1 to 5 am.  I want to extract the hour and the minutes from the date-field.  Here is how it look like my date-field
2015-02-19 00:00:00.000
2015-02-19 00:30:00.000
2015-02-19 01:00:00.000
2015-02-19 01:30:00.000
2015-02-19 02:00:00.000
2015-02-19 02:30:00.000
2015-02-19 03:00:00.000

Open in new window

This is from 12:00 AM to 3:00 AM so i would expect the total to be 3 Hours.  
I tried this but not working..
select  datepart(hour, cast(FullDatetime as time)) as totHours
from myTable
where id in (1, 2, 3,4,5, 7)

Open in new window

moe57Asked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
To return hours, with the decimal places to make sure 2.75 doesn't get cut off to 2 ...
SELECT DATEDIFF(minute, Min(tm), Max(tm)) / 60.0 as hours
FROM #tmp

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Really not following this, but if you're asking for a way to calculate the difference in times between the min and max of a given set, then copy-paste the below into your SSMS, execute to verify it works, then modify to meet your needs
CREATE TABLE #tmp (tm datetime) 

INSERT INTO #tmp (tm) 
VALUES 
('2015-02-19 00:00:00.000'), 
('2015-02-19 00:30:00.000'), 
('2015-02-19 01:00:00.000'), 
('2015-02-19 01:30:00.000'), 
('2015-02-19 02:00:00.000'), 
('2015-02-19 02:30:00.000'), 
('2015-02-19 03:00:00.000')

SELECT DATEDIFF(minute, Min(tm), Max(tm))
FROM #tmp
-- WHERE <whatever>

Open in new window

0
 
moe57Author Commented:
Jim,  i have not looked at  your example yet but all what i need is somehow to sum total hours from the example i mentioned above.  The field has date and time so all what i need is to add up all the times and get the total number of hours which in this case is 3 hours.  I hope i am little bit clearer now.  thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define for us at a Barney level of simplicity how the above set of datetime values adds up to three hours.
0
 
moe57Author Commented:
okay, i am happy to do that..
('2015-02-19 00:00:00.000'),  = 12:00 AM
('2015-02-19 00:30:00.000'),  = 12:30 AM
('2015-02-19 01:00:00.000'),  = 1:00 AM
('2015-02-19 01:30:00.000'),  = 1:30 AM
('2015-02-19 02:00:00.000'),  = 2:00 AM
('2015-02-19 02:30:00.000'),  = 2:30 AM
('2015-02-19 03:00:00.000')  =  3:00 AM
So when you add up from 12:00 am to 3:00 Am  total hours = 3 hours.  I am using military hours.  Thanks
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
That was provided to you in my first comment, where it takes the difference between the minimum and maximum values, then does a DATEDIFF to convert to minutes.  The second comments converts to hours with a decimal value.

If that's not what you need, explain again how the calculation is supposed to work.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I think where we may be tripping up here is the use of 'sum up', which implies summing all rows, i.e. midnight + 12:30 + 1 + 1:30 + 2 + 2:30 + 3 would equal 10:30.

I interpreted your question as the maximum value minus the minimum value.

If your definition of 'sum up' is different from the above two, spell it out in more clear language.
0
 
moe57Author Commented:
Jim, thanks for your help.  You did answer my question i just did not see your second answer where you were dividing 60.  Thanks
0
 
moe57Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.