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
