Dates - Calculating the working days in a month excluding holidays

Create an SQL function that will calculate the number of working days (excluding holidays) within a month in SQL Server 2008

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 and adds 1 to @Count where it is not a Saturday, Sunday or in your database of holiday days (which are different per country).

SQL

CREATE FUNCTION [dbo].[CalcWorkDaysMonth](@Month As DateTime) 

RETURNS INT AS BEGIN 

DECLARE @StartDate DATE=CONVERT(DATE,DATEADD(DAY,1-DAY(@Month),@Month)) 

DECLARE @EndDate DATE=DATEADD(MONTH,1,@StartDate) 

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.CalcWorkDaysMonth('2014-01-01') --22 

SELECT dbo.CalcWorkDaysMonth('2014-02-04') --20 

SELECT dbo.CalcWorkDaysMonth('2014-05-15') --20 (22-2)

Ousia Logo