Add working days to date in an SQL Server Function with flexible options
A flexible and re-usable SQL Server function that will add a number of days to a date with the option to exclude holidays or weekends
Create Holidays Table
If you have checked out the other date articles then you may already have the table, if not then use the code below to create it.
These are based on standard holidays in England and Wales.
On the main page there are functions for other Countries.
Create Table
CREATE TABLE Dates.Calendar(
CalendarDate DATETIME2 NOT NULL CONSTRAINT PK_CalendarDate PRIMARY KEY,
CalendarCA AS (DATEDIFF(DAY,DATEADD(DAY,1-DATEPART(DAY,CalendarDate),CalendarDate),CalendarDate)/7)+1 PERSISTED,
CalendarCD AS (DATEDIFF(DAY,CalendarDate,DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,CalendarDate),CalendarDate))))/7)+1 PERSISTED,
WeekDayID AS (DATEPART(weekday,[CalendarDate])),
WeekDayName AS (case DATEPART(weekday,[CalendarDate]) when (1) then 'Sunday' when (2) then 'Monday' when (3) then 'Tuesday' when (4) then 'Wednesday' when (5) then 'Thursday' when (6) then 'Friday' when (7) then 'Saturday' end))
GO
DECLARE @D DATETIME2='1850-01-01'
WHILE @D<='2099-12-31' BEGIN
INSERT INTO Dates.Calendar(CalendarDate) SELECT @D
SET @D=DATEADD(DAY,1,@D)
END
GO
CREATE TABLE Dates.CalendarHolidays(CalendarDate DATETIME2 NOT NULL,CalendarFunction INT NOT NULL,HolidayType VARCHAR(100) NULL,CONSTRAINT PK_Holidays_Id PRIMARY KEY(CalendarDate,CalendarFunction))
GO
/*English & Welsh Holidays*/
INSERT INTO Dates.CalendarHolidays
SELECT CalendarDate,0,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=1 UNION --New Years Day
SELECT CalendarDate,0,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION--Good Friday
SELECT CalendarDate,0,'Easter Monday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,1,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION--Easter Monday
SELECT CalendarDate,0,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION--May Holidays
SELECT CalendarDate,0,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCD=1) UNION--August Holidays
SELECT CalendarDate,0,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION --Christmas Day
SELECT CalendarDate,0,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26 --Boxing Day
GOCreate Function
Now we have the data, we can create a function that loops through each day from the start to the end and adds 1 to @Count where it is not a Saturday, Sunday or in your database of holiday days.
We had some feedback that the previous function could be made flexible, and a revisit to some additional client needs we have updated this with more options:
- @AdjustDate - The date you want to change
- @CalenderFunction - The holiday function you want to use (some Countries in the UK have different dates, so we can store them in different functions)
- @AdjustDats - The number of days to add or remove from the base date
- @AdjustMode - 0 for add days, 1 for subtract days
- @AdjustWeekend - Excludes weekends from your calculations
- @AdjustHolidays - Excludes holidays if the holiday function matches
SQL
CREATE FUNCTION Dates.GetDateAdjusted(@AdjustDate AS DATETIME2,@CalendarFunction INT,@AdjustDays AS INT,@AdjustMode BIT,@AdjustWeekEnds BIT,@AdjustHolidays BIT) RETURNS DATETIME2 AS BEGINSELECT @AdjustDate=DATEADD(DAY,(CASE @AdjustMode WHEN 0 THEN -1 ELSE 1 END),@AdjustDate),@AdjustDays=(CASE @AdjustMode WHEN 0 THEN @AdjustDays+1 ELSE (0-@AdjustDays)-1 END)DECLARE @AdjustCount INT=0,@AdjustWorkDays INT=0,@Date DATETIME2=@AdjustDate/*Add Days*/WHILE @AdjustMode=0 AND @AdjustWorkDays < @AdjustDaysBEGIN SET @AdjustCount=@AdjustCount+1 SET @Date=DATEADD(DAY,@AdjustCount,@AdjustDate) IF NOT ((DATEPART(WEEKDAY,@Date) IN (1,7) AND @AdjustWeekEnds=1) OR EXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunction AND @AdjustHolidays=1)) BEGIN SET @AdjustWorkDays = @AdjustWorkDays + 1 ENDEND/*Subtract Days*/WHILE @AdjustMode=1 AND @AdjustWorkDays > @AdjustDaysBEGIN SET @AdjustCount=@AdjustCount-1 SET @Date=DATEADD(DAY,@AdjustCount,@AdjustDate) IF NOT ((DATEPART(WEEKDAY,@Date) IN (1,7)AND @AdjustWeekEnds=1) OR EXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunction AND @AdjustHolidays=1)) BEGIN SET @AdjustWorkDays = @AdjustWorkDays - 1 ENDENDRETURN @DateEND GOSELECT Dates.GetDateAdjusted('2014-05-01',0,1,0,1,1)--'2014-05-02'SELECT Dates.GetDateAdjusted('2014-05-01',0,2,0,0,0)--'2014-05-03'SELECT Dates.GetDateAdjusted('2014-05-01',0,2,0,1,1)--'2014-05-06'SELECT Dates.GetDateAdjusted('2014-05-01',0,3,0,1,1)--'2014-05-07'SELECT Dates.GetDateAdjusted('2014-05-01',0,4,0,1,1)--'2014-05-08'SELECT Dates.GetDateAdjusted('2014-05-01',0,5,0,1,1)--'2014-05-09'SELECT Dates.GetDateAdjusted('2014-05-01',0,6,0,1,1)--'2014-05-12'