Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Function to determine business hours Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, October 13, 2007 6:10 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, July 1, 2015 11:33 AM Points: 181, Visits: 727
 Comments posted to this topic are about the item Function to determine business hours
Post #410454
 Posted Saturday, January 10, 2009 5:52 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, May 22, 2015 4:07 PM Points: 7, Visits: 159
 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
Post #634142
 Posted Tuesday, May 4, 2010 2:03 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, January 11, 2011 4:47 AM Points: 1, Visits: 13
 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.
Post #915639
 Posted Monday, March 30, 2015 11:23 PM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, July 1, 2015 9:38 AM Points: 1,388, Visits: 1,812
 WHat should be modified here if starttime is 9:00AM and endtime is 6:00PM and lunch is 12:00PM to 1:00PMI 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
Post #1672971

 Permissions