Solved

SQL Server: Determine Hours that elapsed between two different times

Posted on 2016-09-23
4
44 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
  • 2
4 Comments
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 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 26

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 45

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now