Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server: Determine Hours that elapsed between two different times

Posted on 2016-09-23
4
Medium Priority
?
65 Views
Last Modified: 2016-09-26
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
Comment
Question by:Ryan Simmons
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 41812396
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41812532
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41812542
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
 

Author Closing Comment

by:Ryan Simmons
ID: 41816572
Thanks! That works perfectly.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question