February 16, 2016 at 2:05 pm
The link I posted shows 221 lines of holidays in the U.S. in 2016:
That would tip it toward asking the appropriate person(s) what should be followed.
Date Weekday Holiday name Holiday type Where it is observed
Jan 1 Friday New Year's Day Federal Holiday
Jan 6 Wednesday Epiphany Christian
Jan 7 Thursday Orthodox Christmas Day Orthodox
Jan 13 Wednesday Stephen Foster Memorial Day Observance
Jan 14 Thursday Orthodox New Year Orthodox
Jan 15 Friday Lee Jackson Day State holiday Virginia
Jan 18 Monday Martin Luther King Day Federal Holiday
Jan 18 Monday Robert E Lee's Birthday State holiday Alabama, Arkansas
Jan 18 Monday State Holiday State holiday Mississippi
Jan 18 Monday Idaho Human Rights Day State holiday Idaho
Jan 18 Monday Civil Rights Day State holiday Arizona, New Hampshire
Jan 19 Tuesday Robert E Lee's Birthday State holiday Florida
Jan 19 Tuesday State Holiday State holiday Texas
Jan 25 Monday Tu Bishvat/Tu B'Shevat Jewish holiday
Jan 29 Friday Kansas Day Observance
Feb 1 Monday National Freedom Day Observance
Feb 2 Tuesday Groundhog Day Observance
Feb 4 Thursday Rosa Parks Day Local observance California, Ohio
Feb 5 Friday National Wear Red Day Observance
Feb 8 Monday Chinese New Year Observance
Feb 9 Tuesday Shrove Tuesday/Mardi Gras State holiday Alabama, Louisiana
Feb 9 Tuesday Shrove Tuesday/Mardi Gras Local observance Florida
Feb 9 Tuesday Shrove Tuesday/Mardi Gras Observance
Feb 10 Wednesday Ash Wednesday Christian
Feb 12 Friday Lincoln's Birthday State holiday CT, IL, MO, NY
Feb 12 Friday Lincoln's Birthday Local observance Florida
Feb 14 Sunday Valentine's Day Observance
Feb 14 Sunday Statehood Day in Arizona Local observance Arizona
Feb 15 Monday Presidents' Day Federal Holiday
Feb 15 Monday Daisy Gatson Bates Day State holiday Arkansas
Feb 15 Monday Susan B Anthony's Birthday Local observance CA, FL, NY, WI
Feb 28 Sunday Linus Pauling Day Local observance Oregon
Mar 1 Tuesday St. David's Day Observance
Mar 1 Tuesday Town Meeting-dag Vermont State holiday
Mar 2 Wednesday Texas Independence Day State holiday Texas
Mar 2 Wednesday Read Across America Day Observance
Mar 4 Friday Employee Appreciation Day Observance
Mar 7 Monday Casimir Pulaski Day State holiday Illinois
Mar 13 Sunday Daylight Saving Time starts Clock change/Daylight Saving Time
Mar 17 Thursday St. Patrick's Day Observance
Mar 17 Thursday Evacuation Day State holiday Massachusetts
Mar 20 Sunday Palm Sunday Christian
Mar 20 Sunday March equinox Season
Mar 24 Thursday Maundy Thursday Christian
Mar 24 Thursday Purim Jewish holiday
Mar 25 Friday Good Friday State holiday 13 states
Mar 25 Friday Maryland Day Local observance Maryland
Mar 25 Friday Prince Jonah Kuhio Kalanianaoles dag observed State holiday Hawaii
Mar 26 Saturday Holy Saturday Christian
Mar 26 Saturday Prince Jonah Kuhio Kalanianaoles dag State holiday Hawaii
Mar 27 Sunday Easter Sunday Observance, Christian
Mar 28 Monday Easter Monday Christian
Mar 28 Monday Seward's Day State holiday Alaska
Mar 31 Thursday César Chávez Day State holiday CA, CO*, TX*
Apr 1 Friday Pascua Florida Day observed Local observance Florida
Apr 2 Saturday Pascua Florida Day Local observance Florida
Apr 6 Wednesday National Tartan Day Observance
Apr 12 Tuesday National Library Workers' Day Observance
Apr 13 Wednesday Thomas Jefferson's Birthday Observance
Apr 15 Friday Father Damien Day Local observance Hawaii
Apr 15 Friday Emancipation Day observed State holiday District of Columbia
Apr 16 Saturday Emancipation Day State holiday District of Columbia
Apr 18 Monday Tax Day Observance
Apr 18 Monday Patriot's Day State holiday Maine, Massachusetts
Apr 21 Thursday San Jacinto Day State holiday Texas
Apr 22 Friday Oklahoma Day Local observance Oklahoma
Apr 23 Saturday Passover (first day) Jewish holiday
Apr 25 Monday Confederate Memorial Day State holiday Alabama, Florida
Apr 25 Monday State Holiday State holiday Georgia
Apr 25 Monday State Holiday State holiday Mississippi
Apr 26 Tuesday State Holiday Local observance Florida
Apr 27 Wednesday Administrative Professionals Day Observance
Apr 28 Thursday Take our Daughters and Sons to Work Day Observance
Apr 29 Friday Orthodox Good Friday Orthodox
Apr 29 Friday Arbor Day State holiday Nebraska
Apr 30 Saturday Last Day of Passover Jewish holiday
Apr 30 Saturday Orthodox Holy Saturday Orthodox
May 1 Sunday Orthodox Easter Orthodox
May 1 Sunday Law Day Observance
May 1 Sunday Loyalty Day Observance
May 2 Monday Orthodox Easter Monday Orthodox
May 3 Tuesday Primary Election Day Indiana State holiday Indiana
May 4 Wednesday Yom HaShoah Jewish holiday
May 4 Wednesday Kent State Shootings Remembrance Local observance Ohio
May 4 Wednesday Rhode Island Independence Day Local observance Rhode Island
May 5 Thursday Ascension Day Christian
May 5 Thursday Isra and Mi'raj Muslim
May 5 Thursday Cinco de Mayo Observance
May 5 Thursday National Day of Prayer Observance
May 6 Friday National Nurses Day Observance
May 7 Saturday National Explosive Ordnance Disposal (EOD) Day Observance
May 8 Sunday Mother's Day Observance
May 8 Sunday Truman Day State holiday Missouri
May 9 Monday Truman Day observed State holiday Missouri
May 10 Tuesday State Holiday State holiday South Carolina
May 10 Tuesday State Holiday Local observance North Carolina
May 10 Tuesday Primary Election Day West Virginia State holiday West Virginia
May 12 Thursday Yom Ha'atzmaut Jewish holiday
May 15 Sunday Pentecost Christian
May 15 Sunday Peace Officers Memorial Day Observance
May 16 Monday Whit Monday Christian
May 20 Friday National Defense Transportation Day Observance
May 21 Saturday Armed Forces Day Observance
May 22 Sunday Trinity Sunday Christian
May 22 Sunday National Maritime Day Observance
May 22 Sunday Harvey Milk Day Local observance California
May 25 Wednesday Emergency Medical Services for Children Day Observance
May 25 Wednesday National Missing Children's Day Observance
May 26 Thursday Corpus Christi Christian
May 26 Thursday Lag BaOmer Jewish holiday
May 30 Monday Memorial Day Federal Holiday
May 30 Monday Jefferson Davis Birthday Local observance Mississippi
Jun 1 Wednesday Statehood Day Local observance Kentucky, Tennessee
Jun 3 Friday Jefferson Davis Birthday Local observance Florida
Jun 6 Monday Jefferson Davis Birthday State holiday Alabama
Jun 6 Monday D-Day Observance
Jun 7 Tuesday Ramadan starts Muslim
Jun 10 Friday Kamehameha Day observed State holiday Hawaii
Jun 11 Saturday Kamehameha Day State holiday Hawaii
Jun 12 Sunday Shavuot Jewish holiday
Jun 14 Tuesday U.S. Army Birthday Observance
Jun 14 Tuesday Flag Day Observance
Jun 17 Friday Bunker Hill Day State holiday Massachusetts
Jun 19 Sunday Father's Day Observance
Jun 19 Sunday Juneteenth Local observance All except Am. Samoa, AZ, HI, MD, MP, MT, ND, NH, SD, TX, UT, Virg. Is.
Jun 19 Sunday Emancipation Day State holiday Texas
Jun 20 Monday June Solstice Season
Jun 20 Monday West Virginia Day State holiday West Virginia
Jun 20 Monday American Eagle Day Observance
Jul 2 Saturday Lailat al-Qadr Muslim
Jul 4 Monday Independence Day Federal Holiday
Jul 7 Thursday Eid al-Fitr Muslim
Jul 24 Sunday Pioneer Day State holiday Utah
Jul 24 Sunday Parents' Day Observance
Jul 25 Monday Pioneer Day observed State holiday Utah
Aug 1 Monday Colorado Day Local observance Colorado
Aug 4 Thursday U.S. Coast Guard Birthday Observance
Aug 7 Sunday Purple Heart Day Observance
Aug 8 Monday Victory Day State holiday Rhode Island
Aug 14 Sunday Tisha B'Av Jewish holiday
Aug 15 Monday Assumption of Mary Christian
Aug 16 Tuesday Bennington Battle Day State holiday Vermont
Aug 19 Friday Statehood Day in Hawaii State holiday Hawaii
Aug 19 Friday National Aviation Day Observance
Aug 21 Sunday Senior Citizens Day Observance
Aug 26 Friday Women's Equality Day Observance
Aug 27 Saturday Lyndon Baines Johnson Day State holiday Texas
Sep 5 Monday Labor Day Federal Holiday
Sep 9 Friday California Admission Day Local observance California
Sep 10 Saturday Carl Garner Federal Lands Cleanup Day Observance
Sep 11 Sunday Patriot Day Observance
Sep 11 Sunday National Grandparents Day Observance
Sep 13 Tuesday Eid al-Adha Muslim
Sep 16 Friday Constitution Day and Citizenship Day observed Observance
Sep 16 Friday National POW/MIA Recognition Day Observance
Sep 17 Saturday Constitution Day and Citizenship Day Observance
Sep 18 Sunday Air Force Birthday Observance
Sep 22 Thursday September equinox Season
Sep 22 Thursday Emancipation Day Local observance Ohio
Sep 23 Friday Native Americans' Day Local observance California
Sep 25 Sunday Gold Star Mother's Day Observance
Oct 3 Monday Rosh Hashana State holiday Texas
Oct 3 Monday Rosh Hashana Jewish holiday
Oct 3 Monday Muharram Muslim
Oct 3 Monday Child Health Day Observance
Oct 4 Tuesday Feast of St Francis of Assisi Christian
Oct 9 Sunday Leif Erikson Day Observance
Oct 10 Monday Columbus Day Local observance Florida
Oct 10 Monday Columbus Day Federal Holiday All except AK, AR, CA, DE, FL, HI, MI, MN, ND, NV, OR, SD, TX, VT, WA, WI, WY
Oct 10 Monday Native Americans' Day Local observance South Dakota
Oct 10 Monday Indigenous People's Day Local observance CA*, MN*, WA*
Oct 12 Wednesday Yom Kippur State holiday Texas
Oct 12 Wednesday Yom Kippur Jewish holiday
Oct 13 Thursday U.S. Navy Birthday Observance
Oct 15 Saturday White Cane Safety Day Observance
Oct 17 Monday First Day of Sukkot Jewish holiday
Oct 17 Monday Boss's Day Observance
Oct 18 Tuesday Alaska Day State holiday Alaska
Oct 23 Sunday Last Day of Sukkot Jewish holiday
Oct 24 Monday Shmini Atzeret Jewish holiday
Oct 25 Tuesday Simchat Torah Jewish holiday
Oct 28 Friday Nevada Day State holiday Nevada
Oct 29 Saturday Diwali/Deepavali Observance
Oct 31 Monday Halloween Observance
Nov 1 Tuesday All Saints' Day Christian
Nov 2 Wednesday All Souls' Day Christian
Nov 6 Sunday Daylight Saving Time ends Clock change/Daylight Saving Time
Nov 8 Tuesday Election Day Observance
Nov 8 Tuesday Election Day State holiday 14 states
Nov 10 Thursday Marine Corps Birthday Observance
Nov 10 Thursday Return Day Delaware State holiday Delaware
Nov 11 Friday Veterans Day Federal Holiday
Nov 24 Thursday Thanksgiving Day Federal Holiday
Nov 25 Friday State Holiday State holiday Georgia
Nov 25 Friday Presidents' Day State holiday New Mexico
Nov 25 Friday Lincoln's Birthday/Lincoln's Day State holiday Indiana, West Virginia
Nov 25 Friday Black Friday State holiday 24 states
Nov 25 Friday American Indian Heritage Day State holiday Maryland
Nov 27 Sunday First Sunday of Advent Christian
Nov 28 Monday Cyber Monday Observance
Dec 6 Tuesday St Nicholas' Day Observance
Dec 7 Wednesday Pearl Harbor Remembrance Day Observance
Dec 8 Thursday Feast of the Immaculate Conception Christian
Dec 12 Monday The Prophet's Birthday Muslim
Dec 12 Monday Feast of Our Lady of Guadalupe Observance
Dec 13 Tuesday U.S. National Guard Birthday Observance
Dec 17 Saturday Pan American Aviation Day Observance
Dec 17 Saturday Wright Brothers Day Observance
Dec 21 Wednesday December Solstice Season
Dec 23 Friday Christmas Eve observed State holiday KS*, LA, MI, NC, ND, OK, SC, TX, VA*, WI
Dec 24 Saturday Christmas Eve Observance, Christian
Dec 24 Saturday Christmas Eve State holiday KS*, LA, MI, NC, ND, OK, SC, TX, VA*, WI
Dec 25 Sunday Chanukah/Hanukkah (first day) Jewish holiday
Dec 25 Sunday Christmas Day Federal Holiday
Dec 26 Monday Kwanzaa (until Jan 1) Observance
Dec 26 Monday Christmas Day observed Federal Holiday
Dec 26 Monday Day After Christmas Day State holiday KS, KY, NC, NC, NH, SC, TX
Dec 30 Friday New Year's Eve observed State holiday LA, MI, WI
Dec 31 Saturday New Year's Eve Observance
Dec 31 Saturday New Year's Eve State holiday LA, MI, WI
412-977-3526 call/text
February 16, 2016 at 2:26 pm
SQLNightOwl (2/16/2016)
Not all companies have the benefit of having a Data Steward. I've done this for a number of years for both large and small companies. My experience is that the Data Steward position is filled part-time by someone that's fortunate to spend 4 hours a year in the role. 😀There are benefits to dynamically creating the data (i.e. no storage) just as there are benefits to storing the data (no calculation). If you walk into a position and there's nothing in place, then this article provides a starting point. The consultants favorite phrase definitely applies here... it depends on what you situation is. Your implementation of this code will vary according to the needs of your application. I'd definitely pre-calculate and store the data in a Data Warehouse environment. If all you'd doing is putting up a list of holidays your company observes then - maybe. Is the company huge with millions of hits for that info, is this info used in as part of an audited system, or is this a more modest enterprise?
I've yet to see a benefit to dynamically creating the data that is not significantly outweighed by:
1. getting the calculations wrong (give the job to 2 different people if you don't believe me)
2. missing changes in statutory or business calendars (happens as soon as the author of the proc gets a new job)
3. the simple observation that asking a business analyst for a calendar spreadsheet holding stat holidays, fiscal year info etc. is usually faster and way easier to debug.
for me, in a net-new situation, the first item on this issue is to create a calendar table, using input from the business. Then, set up an easy-to-follow process to update it as needed (usually a few years in advance). Oh btw, I currently work for a very large company (100,000s employess, billions in revenue).
Gerald Britton, Pluralsight courses
February 16, 2016 at 5:24 pm
g.britton (2/16/2016)
The thing is, I've always found it necessary to have human eyes on the date table. Things change from year to year (even the start/end of a fiscal year and what days are statutory holidays).
Although I have not generally found a need for human eyes, I do also prefer to actually create a real table and index it like crazy (since it should be getting no regular inserts or updates). Every place I've worked, I've made the table a little different, because (as we can quickly see from the comments) everybody needs something a little different; but you usually have a few specific queries (is this day a business day, what's the fiscal period, whatever) you want to be able to do fast.
Since I haven't generally had a need for human eyes, I just preload that table with dates from 1753-01-01 up until 9999-12-31. (Sorry, future programmers working on the Y10K bug. If it makes you feel better, not everyone was sure society would even still exist.) If you're in an environment where microseconds count, maybe that's excessive and you want to trim some. If you are lucky enough to be somewhere that modern date types are used extensively, and you're more worried about a failed join, maybe you go all the way back to 0001-01-01. Whatever. Point is, I don't want to be worried that something will fail if my yearly job doesn't run or someone looked further ahead than I anticipated. You do you.
Of course I'm not saying that there might not be places like you describe, g. britton. But I've been lucky enough that I could usually nail down the rules for things even if they changed from year to year.
March 1, 2016 at 7:56 am
This wasn't a post advocating that you change your business process. If you're lucky enough to have a data steward then this post is irrelevant. If you have an alternate solution that's working for you, by all means use that. If you're like me and have walked into many small to mid sized companies that didn't have anything... here's a starting point.
The definition table is expandable in that you can add as many definitions as you like. Simply give it a name, define what month it occurs in, if it occurs on a specific day of the month - or - if it falls on a specific day of the week and which week that is (1=first...5=Last) and it it floats (for holidays occurring on a specific day). So, it you want to define July 4th as US Independence Day or July 4th as UK Loss of Colonies Day - the table and function can support it.
Is it perfect - no, although I've yet to have it let me down. My experience may not be typical. Is this the only solution I use - no. I have incorporated this into a number of solutions that are flexible, recoverable and convenient. Is the list of holidays comprehensive - no. I can define all the holidays I like though. If I'm in Arizona where they celebrate Civil Rights Day rather than MLK's Birthday... It's a simple matter to either add or rename the holiday.
--Paul Hunter
March 8, 2016 at 4:58 pm
Thanks for the massive script.
December 15, 2017 at 7:41 am
We maintain a global holiday table for stock trading and settlement. The main table is supported by a little system with half a dozen tables and a couple of procedures. We've found that trying to calculate holidays fails from time to time in many countries due to idiosyncratic and sometimes non-deterministic holiday rules. Instead of calculating (which we used to do for Canada and the US) we harvest holiday information from many public sources and monitor for problems. We update our info at least 6 months into the future.
April 12, 2019 at 10:24 pm
First off thanks for this script but I think there is a bug calculation the date for Good Friday for 2018. I changed the IsFederal value to 1 for the Good Friday record in the HolidayDefinition table and executed fn_HolidyCalendar but get an error message of 'Conversion failed when converting date and/or time from character string'.
Other years seem to work fine
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply