Cody Short Cody Short - 1 year ago 101
SQL Question

SQL Server Trying to Calculate hour difference on two dates

I'm currently trying to compute the time difference between two dates in a database in hours. For example, 6:00 AM to 8:00 AM is 2 hours.

I have this part solved, but it seems the problem I am having is when I try to compute the time between two entries in my database.

2016-09-21 08:00:00.000 is StartTime

2016-08-16 12:00:00.000 is End Time

Yes, I recognize both are showing separate dates. I would like to be able to compute the time difference between the hours 8:00 AM and 12:00 PM, but I have been unable to do so. Currently, My computation is returning -20.

I would really appreciate some insight into fixing this problem.

Also, here is my current query.

SELECT TherapyGroups.Name as GroupName,
COUNT(*) as NumberAttended,
SUM(DATEDIFF(hh, TherapyDailyNotes.StartTime, TherapyDailyNotes.EndTime)) % 24 as ContactHours
FROM TherapyDailyNotes

Also, I'm using SQL Server 2008, but I am not able to cast to TIME.

Answer Source

DATEPART can extract just the hour value:

  ContactHours = abs(
                   datepart(hour, TherapyDailyNotes.StartTime) - 
                   datepart(hour, TherapyDailyNotes.EndTime)
FROM TherapyDailyNotes
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download