SQLServerCentral.com / Article Discussions / Article Discussions by Author / Discuss content posted by Megan Beebe / Count Business Days / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 17:24:35 GMT20RE: Count Business Dayshttp://www.sqlservercentral.com/Forums/Topic532609-1331-1.aspxCreated the function using copy / paste / execute - no reported error then tested using the following:DECLARE @Bdays AS INTSET @Bdays = dbo.fn_GetBusinessDays('07/06/2008','07/27/2008')SELECT @BdaysResult returned 14 .. Now counting on my fingers it is actually 15 daysFri, 18 Jul 2008 10:55:13 GMTbitbucket-25253RE: Count Business Dayshttp://www.sqlservercentral.com/Forums/Topic532609-1331-1.aspxIf you prepopulate a "calendar" table with your weekend days and holidays, you can also do a simple query as shown in the following example. Please note that the great majority of the code which follows is actually the code used to populate a table variable which on our system is a permanent table . All the work of calculating business days is done in the last three lines. (The tally table is simply a table of numbers [N] from one to a million which I always create in any db. )Having a calendar table also lets you do some cute things such as having a column that designates whether the holiday is for U.S., Canada, or both, which lets your function take a parameter to pass to your where clause to pick the appropriate holidays. You might also want an option to NOT include Saturdays as business days, or to add 1 to the datediff. Different clients count turn times in different ways.------------------------------------------------------------------------------------------------------------------- example-----------------------------------------------------------------------------------------------------------------declare @start datetimedeclare @end datetimedeclare @weekends_holidays table (offday datetime primary key, weekday int)select @start = '7/1/2008',@end = '7/10/2008'-- the following query populates the table variable with some weekend days and the 4th of Julyinsert into @weekends_holidays select distinct @start+N-1,datepart(dw,@start+N-1) from tally where N &lt;= datediff(dd,@start,@end) and datepart(dw,@start+N-1) in (1,7) -- living for the weekend union all select '7/4/2008',datepart(dw,'7/4/2008') -- three cheers for the red, white, and blueselect * from @weekends_holidays--------------------------------------------------------------------------- all of the above was just to set up the following query-- in practice, @weekends_holidays would be a permanent table-------------------------------------------------------------------------select datediff(dd,@start,@end) - count(*) as businessDaysfrom @weekends_holidayswhere offday between @start and @endFri, 18 Jul 2008 08:14:15 GMTThe Dixie FlatlineCount Business Dayshttp://www.sqlservercentral.com/Forums/Topic532609-1331-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/DateDiff/63346/">Count Business Days</A>[/B]Fri, 11 Jul 2008 09:55:53 GMTmegan.beebe