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
