SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Compute Holiday Day of the Month for any Year

By Eric Notheisen,

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.

Total article views: 417 | Views in the last 30 days: 2
Related Articles


I need to find a way to skip holidays for a scheduled job.


Happy Holidays 2012

Merry Christmas and Happy Holidays from SQLServerCentral


Holiday List

A table UDF to return a list of holidays for a given year.


Holiday traditions

Do you have holiday traditions? May this is the year to create some new ones.


Happy Holidays

I hope everyone is enjoying the holidays.  To everyone in the SQL Server community, Merry Christmas...