Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

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
0
Ryan Simmons
Asked:
Ryan Simmons
  • 2
1 Solution
 
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now