SQLServerCentral / Discuss Content Posted by J.D. Gonzalez / Article Discussions / Article Discussions by Author / Function to determine business hours / Latest Posts

RE: Function to determine business hours

guzun.allex — Tue, 04 May 2010 14:03:49 GMT

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.

RE: Function to determine business hours

douglas spencer — Sat, 10 Jan 2009 05:52:53 GMT

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 endselect @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)) endthat should get you there!doug

Function to determine business hours

J.D. Gonzalez — Sat, 13 Oct 2007 18:10:22 GMT

Comments posted to this topic are about the item [B]Function to determine business hours[/B]