To read the article online, visit http://www.4GuysFromRolla.com/webtech/tips/t043006-1.shtml

User Tips: Calculate the Date After a Given Number of Business Days


By Wade Wilbur

An issue came up recently where a client needed to wait a certain number of business days before taking an action. To accomplish this I wrote a user defined function (UDF) in SQL Server 2000.

A business day is defined as any weekday that is not a holiday. Since the client is the US Federal Government, I first built a table and populated it with Federal Holidays for 2006.

-- First you need to create the Federal Holiday Table
drop TABLE [tFederalHoliday]
CREATE TABLE [tFederalHoliday] (
    [HolidayYear] char(4),
    [HolidayDate] [datetime] NULL ,
    [HolidayDescription] [varchar] (30)
)
GO

-- Then you need to populate it
INSERT INTO tFederalHoliday
VALUES('2006',   '1/2/2006', 'New Years')
INSERT INTO tFederalHoliday
VALUES('2006',   '1/16/2006', 'Martin Luther King Jr Birthday')
INSERT INTO tFederalHoliday
VALUES('2006',   '2/20/2006', 'Presidents Day')
INSERT INTO tFederalHoliday
VALUES('2006',   '5/29/2006', 'Memorial Day')
INSERT INTO tFederalHoliday
VALUES('2006',   '7/4/2006', 'Independence Day')
INSERT INTO tFederalHoliday
VALUES('2006',   '9/4/2006', 'Labor Day')
INSERT INTO tFederalHoliday
VALUES('2006',   '10/16/2006', 'Columbus Day')
INSERT INTO tFederalHoliday
VALUES('2006',   '11/13/2006', 'Veterans Day')
INSERT INTO tFederalHoliday
VALUES('2006',   '11/23/2006', 'Thanksgiving')
INSERT INTO tFederalHoliday
VALUES('2006',   '12/25/2006', 'Christmas')

I then built a UDF that receives two input parameters - a date/time parameter (@startdate) that is the date we wish to start counting from and an integer parameter (@days) that represents the number of business days you wish to calculate. This UDF returns a date/time value, namely the date after @days number of business days has expired since @startdate.

--DROP FUNCTION DueDate

--declare function receiving two parameters -
--the date we start counting and the number of business days

CREATE FUNCTION DueDate (@startdate datetime, @days int)
   RETURNS datetime
AS
BEGIN

The UDF operates by using a counter to determine how many days have passed. A variable is declared to hold the ending date, which is returned at the end of the function. A loop increments the counter for each day that is not a weekend or holiday

--declare a counter to keep track of how many days are passing
declare @counter int

/*
Check your business rules.  If 4 business days means you 
count starting tomorrow, set counter to 1.  If you start 
counting today, set counter to 0
*/
set @counter = 1

--declare a variable to hold the ending date
declare @enddate datetime

--set the end date to the start date.  we'll be 
-- incrementing it for each passing business day
set @enddate = @startdate

/*
Start your loop.
While your counter (which was set to 1), is less than 
or equal to the number of business days increment your 
end date
*/
WHILE @counter <= @days

BEGIN

--for each day, we'll add one to the end date
set @enddate = DATEADD(dd, 1, @enddate)

   --If the day is between 2 and 6 (meaning it's a week
   --day and the day is not in the holiday table, we'll 
   --increment the counter
   IF (DATEPART(dw, @enddate) between 2 and 6) AND 
      (@enddate not in 
          (
           select HolidayDate 
           from tFederalHoliday 
           where [HolidayYear] = datepart(yyyy,@enddate)
         )
       )
   BEGIN
      set @counter = @counter + 1
   END

--end the while loop
END

--return the end date
RETURN @enddate


--end the function
END

How to run the function:

select DueDate('4/25/06', 2)

output: 2006-04-27 00:00:00.000

This is a good example of how to solve a common problem by building a function which can be easily used accross multiple applications. If you have any questions, or suggestions for improvements, please feel free to contact me at wade_wilbur@sra.com.

Happy Programming!

  • By Wade Wilbur
    Return to user tips...


  • Article Information
    Article Title: User Tips: Calculate the Date After a Given Number of Business Days
    Article Author: Wade Wilbur
    Article URL: http://www.4GuysFromRolla.com/webtech/tips/t043006-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers