Function to determine business hours

  • Comments posted to this topic are about the item Function to determine business hours

  • There are several bugs to this formula when dealing with minutes and the @enddate is after the business hours. Also, there is an issue with identical minutes and the calucation of hours. The @Cnt needs to be a 1 in the event the difference between the @startdate and @enddate is less than 1 hour.

    These changes should be incorporated into the code:

    select @Cnt=case when datediff(mi,@StartDate,@endDate)%60=0 then 0 else 1 end

    select @strip = case when datepart(hh,@enddate) in (0,1,2,3,4,5,6,7,12,17,18,19,20,21,22,23)

    then (60-datepart(mi,@startdate))*.0167 else convert(decimal(9,2),right(@hoursBetween,3)) end

    that should get you there!


  • I have noticed the same bugs and fixed them in the following way:

    first I adjust StartDate And EndDate if they are not whithin bussines hours

    /*If the start date is after the business hours we will remove the minutes from the StartDate*/

    select @StartDate = case when datepart(hh,@StartDate) in (0,1,2,3,4,5,6,7,12,17,18,19,20,21,22,23)

    then dateadd(mi,-datepart(mi,@StartDate),@StartDate) else @StartDate end

    /*If the End date is after the business hours we will remove the minutes from the EndDate*/

    select @endDate = case when datepart(hh,@endDate) in (0,1,2,3,4,5,6,7,12,17,18,19,20,21,22,23)

    then dateadd(mi,-datepart(mi,@endDate),@endDate) else @endDate end

    then I have changed a bit the while statement

    while @Cnt < FLOOR(@HoursBetween)

    And it works perfectly, thx.

  • WHat should be modified here if starttime is 9:00AM and endtime is 6:00PM and lunch is 12:00PM to 1:00PM

    I will be passing the starttime and endtime as ('2015-01-01 10:20:01.001','2015-01-01 17:29:21.001')

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply