Great responses, all, folks.   Thank you very much for taking the time to make them. 

Hmmmm… perhaps the title of the article should have been “Calculating ‘Week’ Days” instead of calculating “Work” days…

The function in the article wasn’t meant to be an all serving world wide compatible universal function.  Like any of Microsoft’s date functions  , it was meant to do just one thing… solve for the number of Mon-Fri days (normal workdays in the U.S.) in a date range, not including Holidays, without any limits other than the limit Microsoft imposed with their maximum date of the DateTime data-type.  It was meant to do it without the use of cursors, WHILE loops, counters, tables that have 1 one row for each day of the year for several years, or tables that required one row for each weekend day for several years.  It wasn’t meant to tell you the day of the week, what day of the month a date is, or any of the other date functions that are readily available in SQL Server.

That being said, let me see if I can answer a couple of your concerns…

About Holidays:

I agree that holidays are an important part of planning.  As difficult as it would be to design an electronic universal translator that could translate from or to any language or dialect in the world, so it is with Holidays.  As some have pointed out, there are bank holidays for processing, holidays that only certain states in the U.S. take (Rhode Island gets Patriots day off, most other states don’t even know what Patriots day is), company holidays (we get Christmas Eve off, lot’s of companies don’t), national holidays which vary greatly by country, etc, etc.  The function simply wasn’t meant to solve for every possible holiday for every company in every country.  Like I said, I should have titled the article “Calculating ‘Week’ Days”.

One of the many problems I’ve seen with many methods of calculating “work days” has been the creation of some very large but still limited tables usually consisting of one row for each day of the year for several years.  If you consider the function I wrote as a simple function that does one thing well and combine it with a very simple easy to maintain “Holiday” table (<20 rows per year as opposed to 104+ to 365 per year?), you would indeed be able to account for any holiday.  In other words, a holiday table combined with the use of this function would eliminate the need to list weekends in the table.

Another way of putting it is that this function successfully excludes all weekend days.  One of the respondents suggested there are many methods available for doing this but it is those methods that I took exception to because of their poor performance or the maintenance of  “date dimension” tables that had to account for weekends as well as holidays.

One of the respondents to this article correctly suggested that if you could exclude weekends, which this function does, then the holiday table would only contain holidays and a simple SELECT statement could be used to exclude the holidays, as well.  That would be a very easy add-in to the function and all are encouraged to do that to meet the particular needs of their particular country and company.  The add-in to the RESULT of the function might look something like this…

     -- Subtract 1 day for each holiday in the date range

       -(SELECT COUNT(*)

           FROM Holidays

          WHERE HolidayDate >= @StartDate

            AND HolidayDate <= @EndDate

Yup, I know about BETWEEN… that would be the subject of whole different article.

6 Day WorkWeeks

Being in IT, I’d kill to only work six days a week but I get your point.  As previously stated, the function was designed to do only one thing well and, depending on your point of view, it either counts all of the weekdays or it excludes all of the weekend days.  Since a six day work week in India and other countries usually means the workdays are Mon-Sat, a simple modification to the function could be made to the “meat” of the function to calculate 6 day work weeks (or exclude 1 day weekends, whichever) as follows:

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

             -- Modified to handle 6 day Mon-Sat workdays

         RETURN (

                SELECT

              --Start with total number of days including weekends

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

              --Subtact 1 day for each full weekend

               -(DATEDIFF(wk,@StartDate,@EndDate))  -- Times 2 removed

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

              -- Whole statement removed

Of course, if you need both, you could either have two nearly identical functions or you could add a parameter to identify whether to calculate a five day or a six day work week using CASE statements. 

Again, let me stress that the function was not meant to be the end all of work day calculators.  It was only meant to count weekdays of a 5 day workweek or to exclude weekend days without performance problems, table limits, or the maintenance of very large date tables.  You can take the meat of this function and easily add whatever functionality your country and company demands.

Date Dimension Tables:

Although highly customizable, I chose not to use a Date Dimension Table because it didn’t make sense to me to store data like month name, etc, when SQL has so many high speed functions that readily return this data.  And the functions are faster than the retrieval of any data from disk.

Using just a combination of this function (with the holiday mod above), a very simple holiday-only table, and the easy to use date functions readily available in SQL Server, anything you want to know about a date can be derived in a very high speed fashion without the use of large Date Dimension Tables.

As one of the respondents to this article pointed out, you could add a dimension or two to the holiday-only table and make a simple modification to the function to use it if international, interstate, or inter-company holiday calculations were necessary.  This would still be a holiday-only table and that would be much smaller and easier to maintain than a table listing weekend days, holidays, and things that high speed date functions already provide.

Cursors

One of the respondents to this article pointed out that this function “Does so in a way that never even hints at cursor use.”  That, my knowledgeable friend, was one of the main goals of this function and I thank you for recognizing that.  Many of the methods I’ve seen for doing what this function does, use Cursors or RBAR (Row By Agonizing Row) WHILE loops.  There are many articles and many more forum posts that denounce the use of cursors for any reason and I agree.  While I may occasionally use a set based WHILE loop, I have never found reason to use a cursor in SQL Server for any reason (not even for Hierarchies).  Sure, it sometimes takes longer to figure out a different solution (this function was certainly an example of that), but the performance gains always seem to make it all worth while.

Thank You

Thank all of you for responding.  This has been a great first experience for me.  Lots of ideas came out and it shows that people on this forum are always trying to think of a better way.  I’ve met a lot of “cooters” in this business and none of you folks have to worry about becoming one.

Sincerely,