checkholiday.sql

USE [Your Database]
GO

CREATE Function [dbo].[CheckHoliday] (@ReferenceDate date)
returns bit
as
BEGIN
 DECLARE @Holiday bit
 DECLARE @Year int = YEAR(@ReferenceDate)
        ,@Month int = MONTH(@ReferenceDate)
        ,@Day int = DAY(@ReferenceDate)
        ,@Weekday int = DatePart(dw,@ReferenceDate)
 DECLARE @PresidentsDay date = dbo.FloatingDate(3,1,2)
        ,@MemorialDay date = dbo.FloatingDate(-1,2,5)
        ,@July4th date = dbo.HolidayAdjustment(DATEFROMPARTS(@Year,7,4))
        ,@LaborDay date = dbo.FloatingDate(1,1,9)
        ,@Thanksgiving date = dbo.FloatingDate(2,2,10)
        ,@ThanksgivingFriday date = dbo.FloatingDate(2,5,10)
        ,@Christmas date = dbo.HolidayAdjustment(DATEFROMPARTS(@Year,12,25))
        ,@ChristmasEve date = dbo.HolidayAdjustment(DATEFROMPARTS(@Year,12,24))
        ,@NewYearsEve date = dbo.HolidayAdjustment(DATEFROMPARTS(@Year,12,31))
        ,@NewYears date = dbo.HolidayAdjustment(DATEFROMPARTS(@Year,1,1))

 IF @ReferenceDate = @PresidentsDay
    or @ReferenceDate = @MemorialDay 
    or @ReferenceDate = @July4th 
    or @ReferenceDate = @LaborDay
    or @ReferenceDate = @Thanksgiving 
    or @ReferenceDate = @ThanksgivingFriday 
    or @ReferenceDate = @Christmas 
    or @ReferenceDate = @ChristmasEve 
    or @ReferenceDate = @NewYearsEve 
    or @ReferenceDate = @NewYears 
    or DATEPART(dw,@ReferenceDate) = 7
    or DATEPART(dw,@ReferenceDate) = 1
  BEGIN
   SET @Holiday = 1
  END
 ELSE
  BEGIN
   SET @Holiday = 0
  END

 return @Holiday
END

/* Holiday Definitions
Presidents Day: Third Monday of Febuary
Memorial Day: Last Monday of May
Independence Day: July 4th
Labor Day: First Monday of September
Thanksgiving: Second Monday of October
Friday after Thanksgiving: Second Friday of October
Christmas Eve: December 24th
Christmas: December 25th
New Years Eve: December 31st
New Years Eve: January 1st
*/

GO

floatingdate.sql

USE [Your Database]
GO

CREATE Function [dbo].[FloatingDate] (@Order int, @Weekday int, @Month int)
returns date
as
BEGIN
 DECLARE @holiday date
 DECLARE @MonthStart date, @DayModifier int
SET @MonthStart = DATEFROMPARTS(YEAR(GETDATE()), @Month, 1)
SET @DayModifier = (@Order - 1) * 7
IF DATEPART(dw, EOMonth(@MonthStart)) > @Weekday and @Order < 0 
 BEGIN
  SET @DayModifier = @DayModifier - 7
 END
ELSE IF DATEPART(dw, @MonthStart) > @Weekday
 BEGIN
  SET @DayModifier = @DayModifier + 7
 END

IF @Order < 0 
 BEGIN
  SET @Holiday = DATEADD(dd, (@Weekday - DATEPART(dw, EOMONTH(@MonthStart)) + @DayModifier), EOMONTH(@MonthStart))
 END
ELSE
 BEGIN
  SET @Holiday = DATEADD(dd, (@Weekday - DATEPART(dw, @MonthStart) + @DayModifier), @MonthStart)
 END

 RETURN @Holiday
END
GO

holidayadjustment.sql

USE [Your Database]
GO

CREATE Function [dbo].[HolidayAdjustment] (@ReferenceDate int)
returns date
as
BEGIN
	DECLARE @AdjustedHoliday date

	IF DATEPART(dw, @ReferenceDate) = 1
		SET @AdjustedHoliday = DATEADD(dd,1,@ReferenceDate)
	ELSE IF DATEPART(dw, @ReferenceDate) = 7
		SET @AdjustedHoliday = DATEADD(dd, -1, @ReferenceDate)
	ELSE 
		SET @AdjustedHoliday = @ReferenceDate

	RETURN @AdjustedHoliday
END
GO