SQLServerCentral Article

Calculating Work Days

,

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...

Example 1: Raw Code Summary

SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

...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 "Row By Agonizing Row"), 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:

Example 2: DATEDIFF Syntax

DATEDIFF(datepart, startdate, enddate)

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 1st of January to the 10th of January for any year? That's right, 10, if you count the 1st and 10th 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)...

Example 3: DATEDIFF Usage (typical)

SELECT DATEDIFF(dd, '01/01/2005', '01/10/2005')

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)

Example 4: Using DATEDIFF to calculate total number of days

DATEDIFF(dd, @StartDate, @EndDate) + 1

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)...

Example 5: Using DATEDIFF to subtract the total number of weekend days

-DATEDIFF(wk, @StartDate, @EndDate) * 2

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)...

Example 6: Using DATENAME to subtract a first Sunday

-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

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

Example 7: Using DATENAME to subtract a last Saturday

-CASE WHEN DATENAME(dw, @EndDate ) = 'Saturday THEN 1 ELSE 0 END)

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...

Example 8: Production UDF

--===== Change current database to the Master database.
     -- Allows function to be shared by everyone
    USE MASTER
GO
--===== If the function already exists, drop it
     IF EXISTS (
                SELECT *
                  FROM dbo.SYSOBJECTS
                 WHERE ID = OBJECT_ID(N'[dbo].[fn_WorkDays]')
                   AND XType IN (N'FN', N'IF', N'TF')
                )
        DROP FUNCTION [dbo].[fn_WorkDays]
GO
 CREATE FUNCTION dbo.fn_WorkDays
/***************************************************************************************
 Purpose:
 1.  Given any valid start date and end date, this function will calculate and return
     the number of workdays (Mon - Fri).
 2.  Given only a valid start date (end date has DEFAULT in it), this function will
     return a 1 if the start date is a weekday and a 0 if not a weekday.
 Usage:
 1. MASTER.dbo.fn_WorkDays(@StartDate,@EndDate)
 2. MASTER.dbo.fn_WorkDays(@StartDate,DEFAULT)    --Always returns 1 or 0
 3. MASTER.dbo.fn_WorkDays(@EndDate,@StartDate)
 4. MASTER.dbo.fn_WorkDays(@StartDate,@StartDate) --Always returns 1 or 0
 5. MASTER.dbo.fn_WorkDays(@EndDate,@EndDate)     --Always returns 1 or 0
 Notes:
 1.  Holidays are NOT considered.
 2.  Because of the way SQL Server calculates weeks and named days of the week, no
     special consideration for the value of DATEFIRST is given.  In other words, it
     doesn't matter what DATEFIRST is set to for this function.
 3.  If the input dates are in the incorrect order, they will be reversed prior to any
     calculations.
 4.  Only whole days are considered.  Times are NOT used.
 5.  The number of workdays INCLUDES both dates
 6.  Inputs may be literal representations of dates, datetime datatypes, numbers that
     represent the number of days since 1/1/1900 00:00:00.000, or anything else that can
     be implicitly converted to or already is a datetime datatype.
 7.  Undocumented: The DATEPART(dw,date) does not actually count weeks... It counts the
     transition to a Sunday regardless of the DATEFIRST setting.  In essence, it counts
     only whole weekends in any given date range.
 8.  This UDF does NOT create a tally table or sequence table to operate.  Not only is
     it set based, it is truly "tableless".
 Error Indications:
 1.  If either the @StartDate or the @EndDate parameter is an invalid date, the
     following error is returned...
     "Server: Msg 242, Level 16, State 3, Line 3
      The conversion of a char data type to a datetime data type resulted in an
      out-of-range datetime value."
 2.  If either the @StartDate or the @EndDate parameter is a string not resembling a
     date, the following error is returned...
     "Server: Msg 241, Level 16, State 1, Line 3
      Syntax error converting datetime from character string."
 3.  If only one parameter is passed, the following error is returned...
     "Server: Msg 313, Level 16, State 2, Line 3
      An insufficient number of arguments were supplied for the procedure or
      function MASTER.dbo.fn_WorkDays."
 Revisions:
 Rev 00 - 12/12/2004 - Jeff Moden - Initial creation and test.
 Rev 01 - 12/12/2004 - Jeff Moden - Load test, cleanup, document, release.
 Rev 02 - 12/26/2004 - Jeff Moden - Return NULL if @StartDate is NULL or DEFAULT and
                                    modify to be insensitive to DATEFIRST settings.
***************************************************************************************/--======================================================================================
--      Presets
--======================================================================================
--===== Define the input parameters (ok if reversed by mistake)
        (
         @StartDate DATETIME,
         @EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
        )
--===== Define the output data type
RETURNS INT
AS
--======================================================================================
--      Calculate the RETURN of the function
--======================================================================================
  BEGIN
        --===== Declare local variables
        --Temporarily holds @EndDate during date reversal
        DECLARE @Swap DATETIME
        --===== If the Start Date is null, return a NULL and exit
             IF @StartDate IS NULL
                RETURN NULL
        --===== If the End Date is null, populate with Start Date value
             -- so will have two dates (required by DATEDIFF below)
             IF @EndDate IS NULL
                SELECT @EndDate = @StartDate
        --===== Strip the time element from both dates (just to be safe) by converting
             -- to whole days and back to a date.  Usually faster than CONVERT.
             -- 0 is a date (01/01/1900 00:00:00.000)
         SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),
                @EndDate   = DATEADD(dd,DATEDIFF(dd,0,@EndDate)  ,0)
        --===== If the inputs are in the wrong order, reverse them
             IF @StartDate > @EndDate
                SELECT @Swap      = @EndDate,
                       @EndDate   = @StartDate,
                       @StartDate = @Swap
        --===== Calculate and return the number of workdays using the
             -- input parameters.  This is the meat of the function.
             -- This is really just one formula with a couple of parts
             -- that are listed on separate lines for documentation
             -- purposes.
         RETURN (
                SELECT
              --Start with total number of days including weekends
                (DATEDIFF(dd,@StartDate,@EndDate)+1)
              --Subtact 2 days for each full weekend
               -(DATEDIFF(wk,@StartDate,@EndDate)*2)
              --If StartDate is a Sunday, Subtract 1
               -(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'
                      THEN 1
                      ELSE 0
                  END)
              --If EndDate is a Saturday, Subtract 1
               -(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday'
                      THEN 1
                      ELSE 0
                  END)
                )
    END
GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.89 (38)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.89 (38)

You rated this post out of 5. Change rating

Related content