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: Friday, September 30, 2016 7:55 AM Points: 181, Visits: 757
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: Tuesday, October 4, 2016 2:34 PM Points: 1,407, Visits: 1,911
 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