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')) else		DatePart(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]

Rate

Share

Share

Rate