How Many Business Days Are There?

Introduction

I recently saw a post for a query that was problematic for a DBA. The performance of this

query was causing problems for the application and he or she was seeking some assistance. The

crux of the probem was that the query was searching for aggregates and then combining these with

static values to determine the number of business days between two dates. My suggestion was to break the

query up into multiple parts.

The Problem

This brings me to the interesting problem. How do you calculate the number of business days between

two dates? Lots of people use this to calculate shipping or payment dates. Or for simple planning (

ever purchase a 21-day advance ticket?). In the US, there are usually five standard business days each

week (Monday, Tuesday, Wednesday, Thursday, and Friday). Saturday and Sunday are not usually business days.

This is not always true, some companies operate on a six day cycle, but for my explanation, I will stick

with 5 days.

SQL Server has a number of date functions, but none of them will directly determine the number

of business days. So how can you do this?

The Solution

Never fear, I have a solution. If I didn't, the article wouldn't have gotten this far. I decided

to develop my own algorithm since this seems like a simple calculation. Here is the algorithm (implemented

as a function) with a description to follow:

if object_id('fBusinessDays') Is not null
 drop function fbusinessdays
go
create function fBusinessDays (@start datetime, @end datetime)
returns int 
as
begin
/*
Description:
   Function designed to calculate the number of business days 
between two dates.
*/declare 
@wks int
,@days int 
,@sdays int
,@edays int
-- Find the number of weeks between the dates. Subtract 1 
-- since we do not want to count the current week.
select @wks = datediff( week, @start, @end) - 1
-- calculate the number of days in these full wks.
select @days = @wks * 5
-- Get the number of days in the week of the start date. This is the days
-- between Saturday (datepart=7) and the startdate. We also remove the
-- Sunday (datepart=1). If the first day is a Saturday, do not exclude
-- this twice.
if datepart( dw, @start) = 7
select @sdays = 7 - datepart( dw, @start)
else
select @sdays = 7 - datepart( dw, @start) - 1
-- Calculate the days in the last week. These are not included in the
-- week calculation. Since we are starting with the end date, we only
-- remove the Sunday (datepart=1) from the number of days. If the end
-- date is Saturday, correct for this.
if datepart( dw, @end) = 7
select @edays = datepart( dw, @end) - 2
else
select @edays = datepart( dw, @end) - 1
-- Sum everything together.
select @days = @days + @sdays + @edays
return( @days)
end

I deliberately broke out the steps in this function to make it easier to understand.

You could combine some of the steps into fewer lines (even 1 line if you wanted), but

I thought this would be easier to read, so I separated each step.

To develop this algorithm, I started with the most obvious tool for me: a calendar. Then

I randomly picked some dates and counted the number of business days between them. Once I

had completed this step, I started to look for patterns. I knew I had the DATEPART and DATEDIFF

functions, so I kept BOL open to this section for reference.

The first thing I noticed about all the dates I picked was that I could count the number of weeks

between the two dates and arrive at a rough estimate of the number of days between them with a

multiplication by 7. Since T-SQL includes a handy DATEDIFF function that can calculate this for me,

this seemed like a good candidate. The calculation was:

        number of weeks * 7 = estimate of days

However, I do not want days, I wanted business days. Since there are five business days in each week,

I reduced this number to 5. The other item I noticed about this calculation is that the last week was rarely a

full week. Therefore I decided to reduce the number of weeks by one and calculate the days in

the last week separately. The first part of the function performs this step.

Next, I knew I wanted the number of business days from the last week. I wanted to count the end

date as a day, so I quickly ran through all combinations and found that if I subtracted one from the

day of the week number, I got the number of days in the last week. The only exception was if the end

date was a Saturday. A simple IF clause solved this problem along with the T-SQL DATEPART function that

gives me a number for each day of the week starting with Sunday = 1, Monday = 2, etc.

Since I needed to calculate the business days in the end week, I thought I'd also need to calculate

the business days in the beginning week. This is a similar calculation, but we have to account for both

Saturday and Sunday. Again, if a Saturday is passed in, a minor correction is needed.

Conclusion

While this may not be the best solution, it works well and shows how encalsulation (a good

programming practice) can be used by taking advantage of SQL Server 2000's functions. Alternatively,

you could make this a stored procedure for SQL 7 or 6.5 and store the result in a variable. If you hand't

had a chance to look at functions, this is a good start.

I've seen this question asked in a few variations, so I hope that I've solved someone's problem. As always

I welcome feedback and please vote on this article below

Steve Jones

June 2001