none
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.

    Monday, October 22, 2012 7:05 PM

Answers

  • 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

    Monday, October 22, 2012 9:39 PM

All replies

  • 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

    Monday, October 22, 2012 9:17 PM
  • 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

    Monday, October 22, 2012 9:39 PM
  • 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.



    • Edited by Tshindaye Tuesday, October 23, 2012 1:48 PM
    Tuesday, October 23, 2012 12:42 PM
  • 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

    Tuesday, October 23, 2012 2:14 PM
  • 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.


    • Edited by Tshindaye Tuesday, October 23, 2012 3:46 PM update
    Tuesday, October 23, 2012 3:41 PM