Dates - Adding working days to a date excluding holidays

Create a function that will add a number of days (excluding holidays) to a date. This is particularly useful when used as part of invoicing or forecasting systems.

Create Holidays Table

If you have checked out the other articles then you may already have the table, if not then use the code below to create it. These are based on standard UK holidays.

Create Table

CREATE TABLE Holidays(
[Date] DATE CONSTRAINT PK_Holidays_Id PRIMARY KEY,
HolidayType VARCHAR(100) NULL)

INSERT INTO Holidays([Date],HolidayType) SELECT '2013-01-01 00:00:00','New Years Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2013-03-29 00:00:00','Good Friday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2013-04-01 00:00:00','Easter Monday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2013-05-06 00:00:00','Early May Bank Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2013-05-27 00:00:00','Spring Bank Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2013-08-26 00:00:00','Summer Bank Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2013-12-25 00:00:00','XMAS Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2013-12-26 00:00:00','Boxing Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2014-01-01 00:00:00','New Years Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2014-04-18 00:00:00','Good Friday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2014-04-21 00:00:00','Easter Monday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2014-05-05 00:00:00','Early May Bank Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2014-05-26 00:00:00','Spring Bank Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2014-08-25 00:00:00','Summer Bank Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2014-12-25 00:00:00','XMAS Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2014-12-26 00:00:00','Boxing Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2015-01-01 00:00:00','New Years Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2015-04-03 00:00:00','Good Friday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2015-04-06 00:00:00','Easter Monday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2015-05-04 00:00:00','Early May Bank Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2015-05-25 00:00:00','Spring Bank Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2015-08-31 00:00:00','Summer Bank Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2015-12-25 00:00:00','XMAS Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2015-12-28 00:00:00','Boxing Day Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2016-01-01 00:00:00','New Years Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2016-03-25 00:00:00','Good Friday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2016-03-28 00:00:00','Easter Monday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2016-05-02 00:00:00','Early May Bank Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2016-05-30 00:00:00','Spring Bank Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2016-08-29 00:00:00','Summer Bank Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2016-12-26 00:00:00','Baxing Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2016-12-27 00:00:00','XMAS Day Holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2017-01-02 00:00:00','New Year’s Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2017-04-14 00:00:00','Good Friday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2017-04-17 00:00:00','Easter Monday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2017-05-01 00:00:00','Early May bank holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2017-05-29 00:00:00','Spring bank holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2017-08-28 00:00:00','Summer bank holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2017-12-25 00:00:00','Christmas Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2017-12-26 00:00:00','Boxing Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2018-01-01 00:00:00','New Year’s Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2018-03-30 00:00:00','Good Friday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2018-04-02 00:00:00','Easter Monday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2018-05-07 00:00:00','Early May bank holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2018-05-28 00:00:00','Spring bank holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2018-08-27 00:00:00','Summer bank holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2018-12-25 00:00:00','Christmas Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2018-12-26 00:00:00','Boxing Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2019-01-01 00:00:00','New Year’s Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2019-04-19 00:00:00','Good Friday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2019-04-22 00:00:00','Easter Monday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2019-05-06 00:00:00','Early May bank holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2019-05-27 00:00:00','Spring bank holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2019-08-26 00:00:00','Summer bank holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2019-12-25 00:00:00','Christmas Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2019-12-26 00:00:00','Boxing Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2020-01-01 00:00:00','New Year’s Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2020-04-10 00:00:00','Easter Monday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2020-04-13 00:00:00','Early May bank holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2020-05-04 00:00:00','Spring bank holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2020-05-25 00:00:00','Summer bank holiday'
INSERT INTO Holidays([Date],HolidayType) SELECT '2020-12-25 00:00:00','Christmas Day'
INSERT INTO Holidays([Date],HolidayType) SELECT '2020-12-28 00:00:00','Boxing Day'

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

SQL

CREATE FUNCTION [dbo].[CalcWorkDaysAddDays](@StartDate As DateTime,@Days AS INT) 
RETURNS DATE AS BEGIN 
DECLARE @Count INT=0 
DECLARE @WorkDay INT=0 
DECLARE @Date DATE=@StartDate 
WHILE @WorkDay < @Days 
BEGIN 
SET @Count=@Count+1 
SET @Date=DATEADD(DAY,@Count,@StartDate) 
IF NOT (DATEPART(WEEKDAY,@Date) IN (1,7) OR EXISTS (SELECT * FROM Holidays WHERE Date=@Date)) 
       BEGIN 
       SET @WorkDay = @WorkDay + 1 
       END 
END 
RETURN @Date 
END 
GO 
SELECT dbo.CalcWorkDaysAddDays('2014-05-01',1)--'2014-05-02' 
SELECT dbo.CalcWorkDaysAddDays('2014-05-01',2)--'2014-05-06' 
SELECT dbo.CalcWorkDaysAddDays('2014-05-01',3)--'2014-05-07' 
SELECT dbo.CalcWorkDaysAddDays('2014-05-01',4)--'2014-05-08' 
SELECT dbo.CalcWorkDaysAddDays('2014-05-01',5)--'2014-05-09' 
SELECT dbo.CalcWorkDaysAddDays('2014-05-01',6)--'2014-05-12'
Ousia Logo