Working days between dates in an SQL Server Function with flexible options

A flexible and re-usable SQL Server function that will calculate the number of days between two dates with the option to exclude holidays or weekends

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 holidays in England and Wales. On the main page there are functions for other Countries.

SQL

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
GO

Create Function

Now we have the data, we can create a function that loops through each day from the start to the end dates and adds 1 dependent on the variables plugged in as below:

@DateFrom - The starting date for your calculation
@CalendarFunction - The holiday type function you want to use
@DateTo - The end of the date range you want to use
@AdjustMode - Calculate the days between the dates as 0 Exclusive or 1 Inclusive of the first day.
@AdjustWeekend - Excludes weekends from your calculations
@AdjustHolidays - Excludes holidays if the holiday function matches

SQL

CREATE FUNCTION Dates.GetDaysAdjusted(@DateFrom DATETIME2,@CalendarFunction INT,@DateTo AS DATETIME2,@AdjustMode BIT,@AdjustWeekEnds BIT,@AdjustHolidays BIT) RETURNS INT AS BEGIN
/*@AdjustMode 0=Count whole days only,1=Any day counts as 1*/
IF @DateFrom>@DateTo BEGIN
DECLARE @T DATETIME2=@DateTo,@F DATETIME2=@DateFrom
SELECT @DateFrom=@T,@DateTo=@F
END
DECLARE @Count AS INT=0,@DateAs DATETIME2=@DateFrom
WHILE @Date < @DateTo BEGIN
IF ((DATEPART(WEEKDAY,@Date)IN (1,7)AND @AdjustWeekEnds=1)
OR
EXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunctionAND @AdjustHolidays=1))
BEGIN
SELECT @Count = @Count + 1
END
SELECT @Date=DATEADD(DAY,1,@Date)
END
RETURN (DATEDIFF(DAY,@DateFrom,@DateTo)-(@Count))+@AdjustMode
END
GO
SELECT Dates.GetDaysAdjusted('2014-01-01',0,'2014-01-31',1,1,1)--22
SELECT Dates.GetDaysAdjusted('2014-02-01',0,'2014-02-28',1,1,1)--20
SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-02',1,1,1)--2
SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-05',1,1,1)--2
SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-06',1,1,1)--3