CODEWITHSUNDEEP
sqlsql-server
Cody Short
Cody Short

Reputation: 47

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.

Upvotes: 0

Views: 48

Answers (1)

Malk
Malk

Reputation: 12003

DATEPART can extract just the hour value:

SELECT 
  ContactHours = abs(
                   datepart(hour, TherapyDailyNotes.StartTime) - 
                   datepart(hour, TherapyDailyNotes.EndTime)
                 )
FROM TherapyDailyNotes

Upvotes: 2

Related Questions