65.9K
CodeProject is changing. Read more.
Home

Holidays Table and GetWorkDays Function in SQL Server

starIconstarIconstarIconstarIconstarIcon

5.00/5 (2 votes)

Nov 7, 2020

CPOL
viewsIcon

26782

downloadIcon

199

Fast function that excludes weekends and holidays

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'
        --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:

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) 
        --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