Function to determine business hours

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by J.D. Gonzalez » Function to determine business hours

Function to determine business hours

Rate Topic

Display Mode

Topic Options

Author

Message

J.D. Gonzalez

Posted Saturday, October 13, 2007 6:10 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 26, 2013 12:32 PM
Points: 180, Visits: 628

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

Post #410454

douglas spencer

Posted Saturday, January 10, 2009 5:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 7, Visits: 139

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!

doug

Post #634142

guzun.allex

Posted Tuesday, May 04, 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

« Prev Topic | Next Topic »

Permissions