Calculating Work-Days in T-SQL

### Overview

One of the more serious questions in project scheduling, employee planning, and accounting of time is "How many workdays are there?" In other words, given a start date and an end date, how many weekdays (usually a "workday") are there in the date range including the start date and the end date?

The normal response I get to that question varies but has been limited to "It can't be done in SQL", "Ya gotta use a CURSOR", or "You have to build a table". This article will take you through all of the logic behind building a nasty fast "Work Day Calculator" function, the crux of which is a single SELECT statement. A fully commented production-worthy UDF is available at the end of this article.

#### For those in a hurry...

For those of you who are in a hurry, think that real programmers don't document their code, don't care about user-friendliness, or are one of those who reads the last page in a book before reading the rest, let me save you some time... here's the core of the code sans any type of comments, user support, or supporting code. As promised, it's a single SELECT statement...

...and now, for the rest of us...

### Building the parts of the "Work Days" algorithm

#### Beware of slow speeds and inaccuracies

Most of the methods I've seen for doing this included either CURSORs (yeeech!), WHILE loops, or the creation of sequence or date tables. All of these methods either employ (what I call) "RBAR" programming (pronounced as "*ree-bar*" and stands for "**R**ow **B**y **A**gonizing **R**ow"), or the creation and yearly maintenance of what usually turn out to be some rather lengthy but still finite date or sequence tables. There had to be a better way... something that didn't involve time consuming loops or lookups in tables... something direct... something that was nasty fast.

Some of the methods I found are direct but frequently are in error, as well. For example, taking the total number of days in the date range, dividing by 7, and then multiplying by 5 provides a really fast *estimate* for large date ranges but for small ranges, can be quite inaccurate percentage-wise. There had to be a better way... something that is 100% accurate *all* the time... something that doesn't care about Leap Years, whether either of the dates occurred on a weekend or weekday, and something that is easy to understand.

The UDF at the end of this article is 100% accurate all the time, and is, as I said before, nasty fast. Let's start breaking the problem down...

The following sections explain how we're going to create "the basis" of a User Defined Function (UDF) to find the number of work days (week days) in a given range of dates. The basic principle is simple... count up all of the whole days in the date range and then subtract all weekend days.

#### How many whole days in a date range?

There are several ways to figure out how many whole days there in a date range. Many of them are quite cumbersome and some of them are incorrect because they include the "Time" element. I've found that the easiest and most accurate method for calculating whole days is to use SQL Server's "DATEDIFF"

function. The DATEDIFF function uses the following syntactical format:

Let's try using it with something real easy to figure out on our own... how many whole days are there in the date range from the 1^{st} of January to the 10^{th} of January for any year? That's right, 10, if you count the 1^{st} and 10^{th} as part of the date range. Let's try it in code ("dd" is the *datepart* name for "days" and must NOT be included in quotes)...

The code in Example 3 will return the number 9. What went wrong? Nothing? it did exactly what we asked. It subtracted the start date from the end date, kind of like 10 -1 in this case, and came up with the number 9, as expected. The lesson here is that to include both the start date and the end date in the count of days, you have to add 1 to the result. Another way of thinking of it is to ask how many whole days are in the date range from one day to the same day... The answer is, of course, is 1, not 0. We have to add 1 to the answer to calculate the correct number of days

So, **the final formula for counting the whole number of days in a given date range** is as follows (for clarity, the variable definitions and SELECT are not included)

#### What is a "Week"?

When I started to work on this algorithm, my initial inclination was to calculate the whole weeks using DATEDIFF and then to multiply by 5 to get most of the workdays. That turned out to be a problem because the WEEK datepart of DATEDIFF is very strange. Here's what I ran into... the week of 01/25/2005 through 01/31/2005 goes from a Sunday to a Saturday. How many weeks is that? Well, that's 7 days so one would expect DATEPART to return 1 for the number of weeks, right? And it does so correctly. But it also returns a 1 as the number of weeks for the date range of 12/11/2004 through 12/12/2004! That's only two days (Saturday and Sunday)!!! How is it that DATEDIFF thinks of that as a week? Then I tried it with a date range of 12/12/2004 through 12/24/2004, or 13 days and only 1 day short of two weeks. Did DATEDIFF return a 2? No, it returned a 1! And it didn't matter what DATEFIRST (an SQL Server function that identifies which day of the week has the number 1) was set to. Originally, my code got quite long trying to work-around that little problem until I had an epiphany...

DATEDIFF for the WEEK datepart doesn't actually calculate weeks, it calculates the number of times a date range contains dates that represent pairs of Saturdays and Sundays. To think of it in more simple terms, it only counts WHOLE WEEKENDS! (It actually does the count a bit differently but it's helpful to think of it that way for this problem.) Now, *that's* useful!

So, let's write a formula that will **subtract whole weekends (2 days each weekend) from the total number of**

**days** that we previously calculated ("wk" is the *datepart* name for "weeks" and must NOT be included in quotes)...

#### Subtracting "partial" weekends

The only thing left to do is to subtract weekend days that aren't parts of a whole weekend. The only time that will occur is if the start date occurs on a Sunday (previous Saturday not included in the date range) or when the end date occurs on a Saturday (following Sunday not included in the date range).

The following formula **subtracts 1 if the start date occurs on a Sunday **("dw" is the "day-of-week" datepart)...

Likewise, the following formula **subtracts 1 if the end date occurs on a Saturday**...

### Putting it all together

If you put all of the code together, you get what was shown at the beginning of this article (Example 1) and you could quit there. Instead, let's force a couple of things to always be what we expect, add some user-friendliness and commented documentation, and turn it into a UDF.

#### "One picture is worth a thousand words"

Rather than leading you through the development of all of the support functions and user-friendliness of the code, here's the rest of the code for a production UDF. I think you'll find the comments very explanatory...