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
Medium Priority
65 Views
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
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
• 2

LVL 52

Accepted Solution

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
``````
1

LVL 27

Expert Comment

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
``````
1

LVL 52

Expert Comment

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

ID: 41816572
Thanks! That works perfectly.
0

## Featured Post

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.
###### Suggested Courses
Course of the Month12 days, 6 hours left to enroll