Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Holidays Table and GetWorkDays Function in SQL Server

5.00/5 (2 votes)
6 Nov 2020CPOL 26.8K   199  
Fast function that excludes weekends and holidays
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.

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

SQL
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'
        --WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-12-31') = _
             @date THEN 'New Years Eve'
        --WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-11-11') = _
             @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:

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

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

SQL
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) 
        --same week
        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
        --diff weeks
        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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)