Dates - Calculating the working days between two dates excluding holidays in SQL Server

Create a function that will calculate the number of working days (excluding holidays) between two dates.

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.

SQL

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 dates and adds 1 to @Count where it is not a Saturday, Sunday or in your database of holiday days.

SQL

CREATE FUNCTION [dbo].[CalcWorkDaysBetween](@StartDate As DateTime,@EndDate AS DateTime)

RETURNS INT AS BEGIN

SET @EndDate =DATEADD(DAY,1,@EndDate)

DECLARE @Count AS Int= 0

DECLARE @Date As DATE=@StartDate

WHILE @Date < @EndDate

BEGIN

IF (DATEPART(WEEKDAY,@Date)IN(1,7) OR (SELECT Count(*) FROM Holidays WHERE Date=@Date)=1)

  BEGIN

  SELECT @Count = @Count + 1

  END

SELECT @Date=DATEADD(DAY, 1,@Date)

END

 

RETURN DATEDIFF(DAY,@StartDate,@EndDate)- @Count

END

GO

SELECT dbo.CalcWorkDaysBetween('2014-01-01','2014-01-31') --22

SELECT dbo.CalcWorkDaysBetween('2014-02-01','2014-02-28') --20

SELECT dbo.CalcWorkDaysBetween('2014-05-01','2014-05-02') --2

SELECT dbo.CalcWorkDaysBetween('2014-05-01','2014-05-05') --2

SELECT dbo.CalcWorkDaysBetween('2014-05-01','2014-05-06') --3

Ousia Logo