SQL Server: Determine Hours that elapsed between two different times

I have a table in SQL Server and I need to be able to calculate the hours between two different times. The challenge is that every date in the table is 01/01/1900. Sample data is attached.

Example:
Row 2 in the sample states that the store opens at 1900-01-01 5:30 AM and closes 1900-01-01 01:00 AM the next day. The answer is 19.5 Hours.
Row 8 in the sample shows a store that is open 24 hours. The dates and times are 1900-01-01 00:00 to 1900-01-01 00:00.
Row 15 in the sample shows a store that opens at 1900-01-01 6:00 AM and closes at 1900-01-01 23:30 PM. The answer is 17 Hours.

The solution should be able to calculate the hours in the same way as the example. It should be able to handle the time frames that extend past midnight and the ones that do not.
HOO.xlsx
Ryan SimmonsBusiness Analyst IIIAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Something like this?
SELECT *, 
	CASE
		WHEN OpenTime > CloseTime THEN DATEDIFF(MINUTE, OpenTime, DATEADD(day,1,CloseTime))/60.0
		WHEN OpenTime < CloseTime THEN DATEDIFF(MINUTE, OpenTime, CloseTime)/60.0
		ELSE 0
	END NumberHours
FROM HOO

Open in new window

1
 
ZberteocCommented:
Small correction, if starttime=closetime(else branch) should be 24 not 0:
SELECT *, 
	CASE
		WHEN OpenTime > CloseTime THEN DATEDIFF(MINUTE, OpenTime, DATEADD(day,1,CloseTime))/60.0
		WHEN OpenTime < CloseTime THEN DATEDIFF(MINUTE, OpenTime, CloseTime)/60.0
		ELSE 24
	END NumberHours
FROM HOO

Open in new window

1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Small correction, if starttime=closetime(else branch) should be 24 not 0:
Good point.  When I was working on the solution I was only thinking in no working hours at all (is that even possible?).
Cheers
0
 
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
Thanks! That works perfectly.
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.