Workable Holiday Table for SQL

--SELECT COUNT(*) FROM GetHolidayDates(0,@StartDate,@EndDate,1) AS h
--WHERE h.HolidayDate BETWEEN @StartDate AND @EndDate





--CREATE FUNCTION [dbo].[GetHolidayDates] (@HolidayKey AS int, @StartDate AS datetime, @EndDate AS DATETIME, @UsageBitMask AS INT)
--RETURNS @HolidayTable TABLE (HolidayKey int, HolidayDate DATETIME, HolidayName VARCHAR(50))

declare @HolidayKey int = 0,  @StartDate datetime = '01/01/2019', @EndDate datetime = '12/31/2019', @UsageBitMask int =1


DECLARE @HolidayTable TABLE
(
    HolidayKey int,
    HolidayDate DATETIME, HolidayName VARCHAR(50)
)


    -- strip time
    select @StartDate = convert(char(10),@StartDate,101), @EndDate = convert(char(10),@EndDate,101)
    DECLARE @Yr int, @EndYr int, @OffsetKey int
    SET @OffsetKey = isnull((SELECT OffsetKey FROM HolidayDef WHERE HolidayKey = @HolidayKey),0)
    SET @Yr = year(@StartDate) SET @EndYr = year(@EndDate)
    IF @Yr>@EndYr RETURN

    WHILE @Yr<= @EndYr
        BEGIN
--            IF @HolidayKey = 0 OR @HolidayKey = 15 OR @OffsetKey = 15 INSERT INTO @HolidayTable SELECT 15,dbo.Passover(@Yr)
--            IF @HolidayKey = 0 OR @HolidayKey = 18 OR @OffsetKey = 18 INSERT INTO @HolidayTable SELECT 18,dbo.Easter(@Yr)
--            IF @HolidayKey = 0 OR @HolidayKey = 19 OR @OffsetKey = 19 INSERT INTO @HolidayTable SELECT 19,dbo.OEaster(@Yr)
--            IF @HolidayKey = 0 OR @HolidayKey = 45 OR @OffsetKey = 45 INSERT INTO @HolidayTable SELECT 45,dbo.Chanukah(@Yr)
--            IF @HolidayKey = 0 OR @HolidayKey = 54 OR @OffsetKey = 54 INSERT INTO @HolidayTable SELECT 54,dbo.TuBishvat(@Yr)
--            IF @HolidayKey = 0 OR @HolidayKey = 55 OR @OffsetKey = 55 INSERT INTO @HolidayTable SELECT 55,dbo.YomHaAtzmaut(@Yr)
--            IF @HolidayKey = 0 OR @HolidayKey = 56 OR @OffsetKey = 56 INSERT INTO @HolidayTable SELECT 56,dbo.TishaBAv(@Yr)
            INSERT INTO @HolidayTable (HolidayKey, HolidayDate, HolidayName)
            SELECT HolidayKey, cast(str(FixedMonth) + '/' + str(FixedDay) + '/' + str(@Yr) AS datetime), HolidayName
            FROM HolidayDef
            WHERE HolidayType = 'F'
            AND (@HolidayKey = 0 OR @HolidayKey = HolidayKey)
            AND (@UsageBitMask = 0 OR @UsageBitMask & UsageBitMask = @UsageBitMask)

            INSERT INTO @HolidayTable (HolidayKey, HolidayDate, HolidayName)
            SELECT HolidayKey, cast(str(FixedMonth) + '/' + str((7 + DayOfWeek-datepart(dw,cast(str(FixedMonth) + '/01/' + str(@Yr) AS datetime)))%7 + 1) + '/' + str(@Yr) AS datetime) + (WeekOfMonth-1)*7 + Adjustment, HolidayName
            FROM HolidayDef
            WHERE HolidayType = 'M'
            AND (@HolidayKey = 0 OR @HolidayKey = HolidayKey)
            AND (@UsageBitMask = 0 OR @UsageBitMask & UsageBitMask = @UsageBitMask)

            INSERT INTO @HolidayTable (HolidayKey, HolidayDate, HolidayName)
            SELECT H1.HolidayKey, dateadd(dd,H1.Adjustment,HolidayDate), H1.HolidayName
            FROM HolidayDef H1
            JOIN HolidayDef H2
                ON (H1.OffsetKey = H2.HolidayKey)
            JOIN @HolidayTable HT
                ON (HT.HolidayKey = H1.OffsetKey AND year(HolidayDate) = @Yr)
            WHERE H1.HolidayType = 'O'
            AND (@HolidayKey = 0 OR @HolidayKey = H1.HolidayKey)
            AND (@UsageBitMask = 0 OR @UsageBitMask & H1.UsageBitMask = @UsageBitMask)

            SET @Yr = @Yr + 1
        END
        DELETE @HolidayTable WHERE HolidayDate<@StartDate OR HolidayDate>@EndDate OR HolidayKey<>@HolidayKey AND @OffsetKey<>0

        -- Adjust StateHolidays to nearest business day
        if @UsageBitMask = 1
            update @HolidayTable set
                HolidayDate = dateadd(dd,
                    case DATEPART(weekday,HolidayDate)
                        when 1 then 1
                        when 7 then -1
                        else 0
                        end
                    ,HolidayDate)
                   
--        RETURN


select * from @HolidayTable