This article will show how to create a Holidays table and how to use this table to get the number of workdays between two dates.
Introduction
This article will show you how to count workdays in SQL Server.
Using the Code
When a holiday will fall on Saturday
, move it to Friday
and when it falls on Sunday
, move it to Monday
.
create FUNCTION [dbo].[ShiftHolidayToWorkday](@date date)
RETURNS date
AS
BEGIN
IF DATENAME( dw, @Date ) = 'Saturday'
SET @Date = DATEADD(day, - 1, @Date)
ELSE IF DATENAME( dw, @Date ) = 'Sunday'
SET @Date = DATEADD(day, 1, @Date)
RETURN @date
END
Some holidays fall on an exact day and need to be adjusted, others fall on a weekday within a day range.
create FUNCTION [dbo].[GetHoliday](@date date)
RETURNS varchar(50)
AS
BEGIN
declare @s varchar(50)
SELECT @s = CASE
WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year] ) + '-01-01') = _
@date THEN 'New Year'
WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]+1) + '-01-01') = _
@date THEN 'New Year'
WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year] ) + '-07-04') = _
@date THEN 'Independence Day'
WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year] ) + '-12-25') = _
@date THEN 'Christmas Day'
@date THEN 'New Years Eve'
@date THEN 'Veteran''s Day'
WHEN [Month] = 1 AND [DayOfMonth] BETWEEN 15 AND 21 AND [DayName] = 'Monday' _
THEN 'Martin Luther King Day'
WHEN [Month] = 5 AND [DayOfMonth] >= 25 _
AND [DayName] = 'Monday' THEN 'Memorial Day'
WHEN [Month] = 9 AND [DayOfMonth] <= 7 _
AND [DayName] = 'Monday' THEN 'Labor Day'
WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' _
THEN 'Thanksgiving Day'
WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 23 AND 29 AND [DayName] = 'Friday' _
THEN 'Day After Thanksgiving'
ELSE NULL END
FROM (
SELECT
[Year] = YEAR(@date),
[Month] = MONTH(@date),
[DayOfMonth] = DAY(@date),
[DayName] = DATENAME(weekday,@date)
) c
RETURN @s
END
To get holidays for a given year, use this function:
create FUNCTION [dbo].GetHolidays(@year int)
RETURNS TABLE
AS
RETURN (
select dt, dbo.GetHoliday(dt) as Holiday
from (
select dateadd(day, number, convert(varchar,@year) + '-01-01') dt
from master..spt_values
where type='p'
) d
where year(dt) = @year and dbo.GetHoliday(dt) is not null
)
This stored procedure will create Holidays
table and will populate from 1990 to 20 years from now.
create proc UpdateHolidaysTable
as
if not exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Holidays')
create table Holidays(dt date primary key clustered, Holiday varchar(50))
declare @year int
set @year = 1990
while @year < year(GetDate()) + 20
begin
insert into Holidays(dt, Holiday)
select a.dt, a.Holiday
from dbo.GetHolidays(@year) a
left join Holidays b on b.dt = a.dt
where b.dt is null
set @year = @year + 1
end
This function will give you the number of days between two dates excluding weekdays and holidays.
create FUNCTION [dbo].[GetWorkDays](@StartDate DATE = NULL, @EndDate DATE = NULL)
RETURNS INT
AS
BEGIN
IF @StartDate IS NULL OR @EndDate IS NULL
RETURN 0
IF @StartDate >= @EndDate
RETURN 0
DECLARE @Days int
SET @Days = 0
IF year(@StartDate) * 100 + datepart(week, @StartDate) = _
year(@EndDate) * 100 + datepart(week, @EndDate)
select @Days = (DATEDIFF(dd, @StartDate, @EndDate))
- (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
- (select count(*) from Holidays where dt between @StartDate and @EndDate)
ELSE
select @Days = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
- (DATEDIFF(wk, @StartDate, @EndDate) * 2)
- (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
- (select count(*) from Holidays where dt between @StartDate and @EndDate)
RETURN @Days
END
History
- 7th November, 2020: Initial version