Business Days, Hours, Minutes, Seconds between two dates

Question

Hello,

I am having an issue calculating elapsed time in days, hours, minutes and seconds between 2 dates.

The proposed solution should allow for company holidays based and that start and end of business day is as follow:

8h30AM - 5:15PM.

Thanks for helping with this requirement.

Answer 1

Some help

http://stackoverflow.com/questions/2886839/sql-is-there-a-more-efficient-way-to-calculate-elapsed-hours-minutes-seconds

---

Roar Jørstad aka sveroa
Senior Consultant, EVRY as
Blog: Notebook, trick & tips
Please mark as answer or helpful if my post is useful

Answer 2

I believe you will need to create a custom calendar table to meet your requirement. This calendar table needs to then have appropriate data for the holiday schedule of your company. The holiday schedule will vary depending upon if your company follows trading (market) holidays, bank holidays, religious holiday, school holidays, etc. Please refer to this blog post (http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx) for ideas on creating and populating the calendar table. once you have this in place, you can use a combination of various standard TSQL date/time functions to calcluate the time elapsed in days, hours, minutes and seconds between 2 days. Here is an example ; 

SELECT DATEDIFF(DAY, GETDATE(), GETDATE() + 1) 

SELECT DATEDIFF(HOUR, GETDATE(), GETDATE() + 1) 

SELECT DATEDIFF(MINUTE, GETDATE(), GETDATE() + 1)

SELECT DATEDIFF(SECOND, GETDATE(), GETDATE() + 1) 

-- Results

-----------
1

(1 row(s) affected)


-----------
24

(1 row(s) affected)


-----------
1440

(1 row(s) affected)


-----------
86400

(1 row(s) affected)

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 3

Hello Sanil,

Thanks for the update but I am looking for something a little more complexe.

My requirement is to calculate the average Turnaround time for a number of tasks.

I am using the following to get to that result without issues:

sum(cast(ElapsedTimeInSeconds as bigint))/count(Task1)/86400 AS Days,
sum(cast(ElapsedTimeInSeconds as bigint))/count(Task1)%86400/3600 AS Hours,
sum(cast(ElapsedTimeInSeconds as bigint))/count(Task1)%3600/60 AS Minutes,
sum(cast(ElapsedTimeInSeconds as bigint))/count(Task1)%60 AS Seconds

As you can see, I use "ElapsedTimeInSeconds" / "count(Task..n)" to get the average and then I apply the time calculation logic to extract the portions of time I want( days, minutes,...)

Ideally, I would like to use a similar scenario tweaked to take into account holidays (for which we have a table), weekends as well as business hours.

See below for the  holidays table I use:

Holiday          Year HolidayDate                        HolidayDescription
==================================================================

2012             2012-01-02 00:00:00.000               New Years
2012             2012-05-28 00:00:00.000               Memorial Day
2012             2012-07-04 00:00:00.000               Independence Day
2012             2012-09-03 00:00:00.000               Labor Day
2012             2012-11-22 00:00:00.000               Thanksgiving
2012             2012-12-25 00:00:00.000               Christmas

Thanks for assisting again.

Answer 4

Hi,

Thanks for the update with additional details of your scenario - it helps us to provide a better answer.

How do you calculate "ElapsedTimeInSeconds" ? Is it stored in a column - is it a computed field ? Where is the start and end date/time for a particular "ElapsedTimeInSeconds" value ? Based on the information given so far, I believe you will need to work the holidays into the calculation for "ElapsedTimeInSeconds" by finding out if there was a Holiday between that start & end of that particular period.

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 5

Yes indeed, the "ElapsedTimeInSeconds" is a computed field. I get it by using the sql convert function to extract duration in seconds between two periods.

Additionally, the start and end date of any task occur within business hours(8:30AM - 5:15PM) excluding weekends and holidays and therefore "ElapsedTimeInSeconds" should account for it.