Compute Holiday Day of the Month for any Year
This script uses a common table expression to store the list of holidays for use in other processing. You may add additional holidays as necessary. I was using this CTE to compute the next business day as part of a batch process within an SSIS package. The fixed date holidays are unioned with floating date holidays to create a table that is computable regardless of the year. The result looks like this:
| | | | | | | |
| | Holiday | Month | DayOfWeek | Holiday Celebrated | Next Work Day | |
| | New Years Day | 1 | 5 | 1/1/2015 | 1/2/2015 | |
| | MLK | 1 | 5 | 1/19/2015 | 1/20/2015 | |
| | Presidents | 2 | 1 | 2/16/2015 | 2/17/2015 | |
| | Memorial | 5 | 5 | 5/25/2015 | 5/26/2015 | |
| | Independence | 7 | 7 | 7/4/2015 | 7/6/2015 | |
| | Labor | 9 | 5 | 9/7/2015 | 9/8/2015 | |
| | Columbus | 10 | 5 | 10/12/2015 | 10/13/2015 | |
| | Veterans | 11 | 4 | 11/11/2015 | 11/12/2015 | |
| | Thanksgiving | 11 | 5 | 11/26/2015 | 11/27/2015 | |
| | Christmas | 12 | 6 | 12/25/2015 | 12/26/2015 | |
| | | | | | | |
The year selected in htis case was 2015.
Declare @Year int = Year(GetDate()), @NewYearsDay int, @sYear varchar(4) =Cast(@Year as varchar(4))
with CTE_Holiday(Holiday, [Month], DayOfWeek, [Holiday Celebrated], [Next Work Day]) as
(
Select *, case DatePart(dw,[Holiday Celebrated])
when 7 then DateAdd(d, 2,[Holiday Celebrated])
when 1 then DateAdd(d,1,[Holiday Celebrated])
else DateAdd(d,1,[Holiday Celebrated]) end as [Next Work Day]
from(
Select 'New Years Day' as [Holiday], Month(@sYear +'-'+'01'+'-'+'01')as [Month], Case when Month(GetDate())=12 Then DatePart(dw,(Cast(@Year+1 as varchar(4)) +'-'+'01'+'-'+'01')) elseDatePart(dw,@sYear +'-'+'01'+'-'+'01') end as 'dayofweek',
case Case when Month(GetDate())=12 Then DatePart(dw,(Cast(@Year+1 as varchar(4)) +'-'+'01'+'-'+'01')) else DatePart(dw,@sYear +'-'+'01'+'-'+'01') end
when 1 then DateAdd(d, 1,@sYear+'-'+'01'+'-'+'01')
when 7 then DateAdd(d, 3,@sYear+'-'+'01'+'-'+'01')
else @sYear+'-'+'01'+'-'+'01' end as [Holiday Celebrated]
Union
Select 'MLK' as [Holiday],Month(@sYear +'-'+'01'+'-'+'01')as [Month],DatePart(dw,@sYear +'-'+'01'+'-'+'01') as 'dayofweek',
case DatePart(dw,@sYear +'-'+'01'+'-'+'01')
when 1 then DateAdd(d,15,@sYear+'-'+'01'+'-'+'01')
when 2 then DateAdd(d,14,@sYear+'-'+'01'+'-'+'01')
when 3 then DateAdd(d,13,@sYear+'-'+'01'+'-'+'01')
when 4 then DateAdd(d,19,@sYear+'-'+'01'+'-'+'01')
when 5 then DateAdd(d,18,@sYear+'-'+'01'+'-'+'01')
when 6 then DateAdd(d,17,@sYear+'-'+'01'+'-'+'01')
when 7 then DateAdd(d,16,@sYear+'-'+'01'+'-'+'01') end as [Holiday Celebrated] -- Third Monday
Union
Select 'Presidents'as [Holiday],Month(Cast(@Year as varchar(4)) +'-'+'02'+'-'+'01')as [Month],DatePart(dw,Cast(@Year as varchar(4)) +'-'+'02'+'-'+'01')as 'dayofweek',
case DatePart(dw,@sYear +'-'+'02'+'-'+'01')
when 1 then DateAdd(d,15,@sYear+'-'+'02'+'-'+'01')
when 2 then DateAdd(d,14,@sYear+'-'+'02'+'-'+'01')
when 3 then DateAdd(d,13,@sYear+'-'+'02'+'-'+'01')
when 4 then DateAdd(d,19,@sYear+'-'+'02'+'-'+'01')
when 5 then DateAdd(d,18,@sYear+'-'+'02'+'-'+'01')
when 6 then DateAdd(d,17,@sYear+'-'+'02'+'-'+'01')
when 7 then DateAdd(d,16,@sYear+'-'+'02'+'-'+'01') end as [Holiday Celebrated] -- Third Monday
Union
Select 'Memorial'as [Holiday] ,Month(@sYear +'-'+'05'+'-'+'01')as [Month],DatePart(dw,@sYear +'-'+'01'+'-'+'01')as 'dayofweek' ,
case DatePart(dw,@sYear +'-'+'05'+'-'+'01')
when 1 then DateAdd(d,29,@sYear+'-'+'05'+'-'+'01')
when 2 then DateAdd(d,28,@sYear+'-'+'05'+'-'+'01')
when 3 then DateAdd(d,27,@sYear+'-'+'05'+'-'+'01')
when 4 then DateAdd(d,26,@sYear+'-'+'05'+'-'+'01')
when 5 then DateAdd(d,25,@sYear+'-'+'05'+'-'+'01')
when 6 then DateAdd(d,24,@sYear+'-'+'05'+'-'+'01')
when 7 then DateAdd(d,30,@sYear+'-'+'05'+'-'+'01') end as [Holiday Celebrated] --Fourth monday
Union
Select 'Independence'as [Holiday],Month(@sYear +'-'+'07'+'-'+'01')as [Month], DatePart(dw, @sYear +'-'+'07'+'-'+'04')as 'dayofweek', @sYear +'-'+'07'+'-'+'04'as [Holiday Celebrated]
Union
Select 'Labor' as [Holiday],Month(@sYear +'-'+'09'+'-'+'01')as [Month],DatePart(dw,@sYear +'-'+'01'+'-'+'01')as 'dayofweek',
case DatePart(dw,@sYear +'-'+'09'+'-'+'01')
when 1 then DateAdd(d,1,@sYear+'-'+'09'+'-'+'01')
when 2 then DateAdd(d,0,@sYear+'-'+'09'+'-'+'01')
when 3 then DateAdd(d,6,@sYear+'-'+'09'+'-'+'01')
when 4 then DateAdd(d,5,@sYear+'-'+'09'+'-'+'01')
when 5 then DateAdd(d,4,@sYear+'-'+'09'+'-'+'01')
when 6 then DateAdd(d,3,@sYear+'-'+'09'+'-'+'01')
when 7 then DateAdd(d,2,@sYear+'-'+'09'+'-'+'01') end as [Holiday Celebrated] --first monday of September
Union
Select 'Columbus'as [Holiday],Month(Cast(@Year as varchar(4)) +'-'+'10'+'-'+'01')as [Month],DatePart(dw,Cast(@Year as varchar(4)) +'-'+'01'+'-'+'01')as 'dayofweek',
case DatePart(dw,@sYear +'-'+'10'+'-'+'01')
when 1 then DateAdd(d,8,@sYear+'-'+'10'+'-'+'01')
when 2 then DateAdd(d,7,@sYear+'-'+'10'+'-'+'01')
when 3 then DateAdd(d,13,@sYear+'-'+'10'+'-'+'01')
when 4 then DateAdd(d,12,@sYear+'-'+'10'+'-'+'01')
when 5 then DateAdd(d,11,@sYear+'-'+'10'+'-'+'01')
when 6 then DateAdd(d,10,@sYear+'-'+'10'+'-'+'01')
when 7 then DateAdd(d,9,@sYear+'-'+'10'+'-'+'01') end as [Holiday Celebrated] --Second Monday of October
Union
Select 'Veterans'as [Holiday],Month(Cast(@Year as varchar(4)) +'-'+'11'+'-'+'01')as [Month], DatePart(dw, Cast(@Year as varchar(4)) +'-'+'11'+'-'+'11')as 'dayofweek', @sYear +'-'+'11'+'-'+'11'as [Holiday Celebrated]
Union
Select 'Thanksgiving'as [Holiday],Month(Cast(@Year as varchar(4)) +'-'+'11'+'-'+'01')as [Month],DatePart(dw,Cast(@Year as varchar(4)) +'-'+'01'+'-'+'01')as 'dayofweek' ,
case DatePart(dw,@sYear +'-'+'11'+'-'+'01')
when 1 then DateAdd(d,25,@sYear +'-'+'11'+'-'+'01')
when 2 then DateAdd(d,24,@sYear +'-'+'11'+'-'+'01')
when 3 then DateAdd(d,23,@sYear +'-'+'11'+'-'+'01')
when 4 then DateAdd(d,22,@sYear +'-'+'11'+'-'+'01')
when 5 then DateAdd(d,21,@sYear +'-'+'11'+'-'+'01')
when 6 then DateAdd(d,20,@sYear +'-'+'11'+'-'+'01')
when 7 then DateAdd(d,26, @sYear +'-'+'11'+'-'+'01') end as [Holiday Celebrated] --fourth Thursday of November
Union
Select 'Christmas'as [Holiday],Month(@sYear +'-'+'12'+'-'+'01')as [Month], DatePart(dw, @sYear +'-'+'12'+'-'+'25')as 'dayofweek', @sYear +'-'+'12'+'-'+'25' as [Holiday Celebrated]
)a
)
select * from CTE_Holiday order by [Holiday Celebrated]
