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.
DateWeekdayHoliday nameHoliday typeWhere it is observed
Jan 1FridayNew Year's DayFederal Holiday
Jan 6WednesdayEpiphanyChristian
Jan 7ThursdayOrthodox Christmas DayOrthodox
Jan 13WednesdayStephen Foster Memorial DayObservance
Jan 14ThursdayOrthodox New YearOrthodox
Jan 15FridayLee Jackson DayState holidayVirginia
Jan 18MondayMartin Luther King DayFederal Holiday
Jan 18MondayRobert E Lee's BirthdayState holidayAlabama, Arkansas
Jan 18MondayState HolidayState holidayMississippi
Jan 18MondayIdaho Human Rights DayState holidayIdaho
Jan 18MondayCivil Rights DayState holidayArizona, New Hampshire
Jan 19TuesdayRobert E Lee's BirthdayState holidayFlorida
Jan 19TuesdayState HolidayState holidayTexas
Jan 25MondayTu Bishvat/Tu B'ShevatJewish holiday
Jan 29FridayKansas DayObservance
Feb 1MondayNational Freedom DayObservance
Feb 2TuesdayGroundhog DayObservance
Feb 4ThursdayRosa Parks DayLocal observanceCalifornia, Ohio
Feb 5FridayNational Wear Red DayObservance
Feb 8MondayChinese New YearObservance
Feb 9TuesdayShrove Tuesday/Mardi GrasState holidayAlabama, Louisiana
Feb 9TuesdayShrove Tuesday/Mardi GrasLocal observanceFlorida
Feb 9TuesdayShrove Tuesday/Mardi GrasObservance
Feb 10WednesdayAsh WednesdayChristian
Feb 12FridayLincoln's BirthdayState holidayCT, IL, MO, NY
Feb 12FridayLincoln's BirthdayLocal observanceFlorida
Feb 14SundayValentine's DayObservance
Feb 14SundayStatehood Day in ArizonaLocal observanceArizona
Feb 15MondayPresidents' DayFederal Holiday
Feb 15MondayDaisy Gatson Bates DayState holidayArkansas
Feb 15MondaySusan B Anthony's BirthdayLocal observanceCA, FL, NY, WI
Feb 28SundayLinus Pauling DayLocal observanceOregon
Mar 1TuesdaySt. David's DayObservance
Mar 1TuesdayTown Meeting-dag VermontState holiday
Mar 2WednesdayTexas Independence DayState holidayTexas
Mar 2WednesdayRead Across America DayObservance
Mar 4FridayEmployee Appreciation DayObservance
Mar 7MondayCasimir Pulaski DayState holidayIllinois
Mar 13SundayDaylight Saving Time startsClock change/Daylight Saving Time
Mar 17ThursdaySt. Patrick's DayObservance
Mar 17ThursdayEvacuation DayState holidayMassachusetts
Mar 20SundayPalm SundayChristian
Mar 20SundayMarch equinoxSeason
Mar 24ThursdayMaundy ThursdayChristian
Mar 24ThursdayPurimJewish holiday
Mar 25FridayGood FridayState holiday13 states
Mar 25FridayMaryland DayLocal observanceMaryland
Mar 25FridayPrince Jonah Kuhio Kalanianaoles dag observedState holidayHawaii
Mar 26SaturdayHoly SaturdayChristian
Mar 26SaturdayPrince Jonah Kuhio Kalanianaoles dagState holidayHawaii
Mar 27SundayEaster SundayObservance, Christian
Mar 28MondayEaster MondayChristian
Mar 28MondaySeward's DayState holidayAlaska
Mar 31ThursdayCésar Chávez DayState holidayCA, CO*, TX*
Apr 1FridayPascua Florida Day observedLocal observanceFlorida
Apr 2SaturdayPascua Florida DayLocal observanceFlorida
Apr 6WednesdayNational Tartan DayObservance
Apr 12TuesdayNational Library Workers' DayObservance
Apr 13WednesdayThomas Jefferson's BirthdayObservance
Apr 15FridayFather Damien DayLocal observanceHawaii
Apr 15FridayEmancipation Day observedState holidayDistrict of Columbia
Apr 16SaturdayEmancipation DayState holidayDistrict of Columbia
Apr 18MondayTax DayObservance
Apr 18MondayPatriot's DayState holidayMaine, Massachusetts
Apr 21ThursdaySan Jacinto DayState holidayTexas
Apr 22FridayOklahoma DayLocal observanceOklahoma
Apr 23SaturdayPassover (first day)Jewish holiday
Apr 25MondayConfederate Memorial DayState holidayAlabama, Florida
Apr 25MondayState HolidayState holidayGeorgia
Apr 25MondayState HolidayState holidayMississippi
Apr 26TuesdayState HolidayLocal observanceFlorida
Apr 27WednesdayAdministrative Professionals DayObservance
Apr 28ThursdayTake our Daughters and Sons to Work DayObservance
Apr 29FridayOrthodox Good FridayOrthodox
Apr 29FridayArbor DayState holidayNebraska
Apr 30SaturdayLast Day of PassoverJewish holiday
Apr 30SaturdayOrthodox Holy SaturdayOrthodox
May 1SundayOrthodox EasterOrthodox
May 1SundayLaw DayObservance
May 1SundayLoyalty DayObservance
May 2MondayOrthodox Easter MondayOrthodox
May 3TuesdayPrimary Election Day IndianaState holidayIndiana
May 4WednesdayYom HaShoahJewish holiday
May 4WednesdayKent State Shootings RemembranceLocal observanceOhio
May 4WednesdayRhode Island Independence DayLocal observanceRhode Island
May 5ThursdayAscension DayChristian
May 5ThursdayIsra and Mi'rajMuslim
May 5ThursdayCinco de MayoObservance
May 5ThursdayNational Day of PrayerObservance
May 6FridayNational Nurses DayObservance
May 7SaturdayNational Explosive Ordnance Disposal (EOD) DayObservance
May 8SundayMother's DayObservance
May 8SundayTruman DayState holidayMissouri
May 9MondayTruman Day observedState holidayMissouri
May 10TuesdayState HolidayState holidaySouth Carolina
May 10TuesdayState HolidayLocal observanceNorth Carolina
May 10TuesdayPrimary Election Day West VirginiaState holidayWest Virginia
May 12ThursdayYom Ha'atzmautJewish holiday
May 15SundayPentecostChristian
May 15SundayPeace Officers Memorial DayObservance
May 16MondayWhit MondayChristian
May 20FridayNational Defense Transportation DayObservance
May 21SaturdayArmed Forces DayObservance
May 22SundayTrinity SundayChristian
May 22SundayNational Maritime DayObservance
May 22SundayHarvey Milk DayLocal observanceCalifornia
May 25WednesdayEmergency Medical Services for Children DayObservance
May 25WednesdayNational Missing Children's DayObservance
May 26ThursdayCorpus ChristiChristian
May 26ThursdayLag BaOmerJewish holiday
May 30MondayMemorial DayFederal Holiday
May 30MondayJefferson Davis BirthdayLocal observanceMississippi
Jun 1WednesdayStatehood DayLocal observanceKentucky, Tennessee
Jun 3FridayJefferson Davis BirthdayLocal observanceFlorida
Jun 6MondayJefferson Davis BirthdayState holidayAlabama
Jun 6MondayD-DayObservance
Jun 7TuesdayRamadan startsMuslim
Jun 10FridayKamehameha Day observedState holidayHawaii
Jun 11SaturdayKamehameha DayState holidayHawaii
Jun 12SundayShavuotJewish holiday
Jun 14TuesdayU.S. Army BirthdayObservance
Jun 14TuesdayFlag DayObservance
Jun 17FridayBunker Hill DayState holidayMassachusetts
Jun 19SundayFather's DayObservance
Jun 19SundayJuneteenthLocal observanceAll except Am. Samoa, AZ, HI, MD, MP, MT, ND, NH, SD, TX, UT, Virg. Is.
Jun 19SundayEmancipation DayState holidayTexas
Jun 20MondayJune SolsticeSeason
Jun 20MondayWest Virginia DayState holidayWest Virginia
Jun 20MondayAmerican Eagle DayObservance
Jul 2SaturdayLailat al-QadrMuslim
Jul 4MondayIndependence DayFederal Holiday
Jul 7ThursdayEid al-FitrMuslim
Jul 24SundayPioneer DayState holidayUtah
Jul 24SundayParents' DayObservance
Jul 25MondayPioneer Day observedState holidayUtah
Aug 1MondayColorado DayLocal observanceColorado
Aug 4ThursdayU.S. Coast Guard BirthdayObservance
Aug 7SundayPurple Heart DayObservance
Aug 8MondayVictory DayState holidayRhode Island
Aug 14SundayTisha B'AvJewish holiday
Aug 15MondayAssumption of MaryChristian
Aug 16TuesdayBennington Battle DayState holidayVermont
Aug 19FridayStatehood Day in HawaiiState holidayHawaii
Aug 19FridayNational Aviation DayObservance
Aug 21SundaySenior Citizens DayObservance
Aug 26FridayWomen's Equality DayObservance
Aug 27SaturdayLyndon Baines Johnson DayState holidayTexas
Sep 5MondayLabor DayFederal Holiday
Sep 9FridayCalifornia Admission DayLocal observanceCalifornia
Sep 10SaturdayCarl Garner Federal Lands Cleanup DayObservance
Sep 11SundayPatriot DayObservance
Sep 11SundayNational Grandparents DayObservance
Sep 13TuesdayEid al-AdhaMuslim
Sep 16FridayConstitution Day and Citizenship Day observedObservance
Sep 16FridayNational POW/MIA Recognition DayObservance
Sep 17SaturdayConstitution Day and Citizenship DayObservance
Sep 18SundayAir Force BirthdayObservance
Sep 22ThursdaySeptember equinoxSeason
Sep 22ThursdayEmancipation DayLocal observanceOhio
Sep 23FridayNative Americans' DayLocal observanceCalifornia
Sep 25SundayGold Star Mother's DayObservance
Oct 3MondayRosh HashanaState holidayTexas
Oct 3MondayRosh HashanaJewish holiday
Oct 3MondayMuharramMuslim
Oct 3MondayChild Health DayObservance
Oct 4TuesdayFeast of St Francis of AssisiChristian
Oct 9SundayLeif Erikson DayObservance
Oct 10MondayColumbus DayLocal observanceFlorida
Oct 10MondayColumbus DayFederal HolidayAll except AK, AR, CA, DE, FL, HI, MI, MN, ND, NV, OR, SD, TX, VT, WA, WI, WY
Oct 10MondayNative Americans' DayLocal observanceSouth Dakota
Oct 10MondayIndigenous People's DayLocal observanceCA*, MN*, WA*
Oct 12WednesdayYom KippurState holidayTexas
Oct 12WednesdayYom KippurJewish holiday
Oct 13ThursdayU.S. Navy BirthdayObservance
Oct 15SaturdayWhite Cane Safety DayObservance
Oct 17MondayFirst Day of SukkotJewish holiday
Oct 17MondayBoss's DayObservance
Oct 18TuesdayAlaska DayState holidayAlaska
Oct 23SundayLast Day of SukkotJewish holiday
Oct 24MondayShmini AtzeretJewish holiday
Oct 25TuesdaySimchat TorahJewish holiday
Oct 28FridayNevada DayState holidayNevada
Oct 29SaturdayDiwali/DeepavaliObservance
Oct 31MondayHalloweenObservance
Nov 1TuesdayAll Saints' DayChristian
Nov 2WednesdayAll Souls' DayChristian
Nov 6SundayDaylight Saving Time endsClock change/Daylight Saving Time
Nov 8TuesdayElection DayObservance
Nov 8TuesdayElection DayState holiday14 states
Nov 10ThursdayMarine Corps BirthdayObservance
Nov 10ThursdayReturn Day DelawareState holidayDelaware
Nov 11FridayVeterans DayFederal Holiday
Nov 24ThursdayThanksgiving DayFederal Holiday
Nov 25FridayState HolidayState holidayGeorgia
Nov 25FridayPresidents' DayState holidayNew Mexico
Nov 25FridayLincoln's Birthday/Lincoln's DayState holidayIndiana, West Virginia
Nov 25FridayBlack FridayState holiday24 states
Nov 25FridayAmerican Indian Heritage DayState holidayMaryland
Nov 27SundayFirst Sunday of AdventChristian
Nov 28MondayCyber MondayObservance
Dec 6TuesdaySt Nicholas' DayObservance
Dec 7WednesdayPearl Harbor Remembrance DayObservance
Dec 8ThursdayFeast of the Immaculate ConceptionChristian
Dec 12MondayThe Prophet's BirthdayMuslim
Dec 12MondayFeast of Our Lady of GuadalupeObservance
Dec 13TuesdayU.S. National Guard BirthdayObservance
Dec 17SaturdayPan American Aviation DayObservance
Dec 17SaturdayWright Brothers DayObservance
Dec 21WednesdayDecember SolsticeSeason
Dec 23FridayChristmas Eve observedState holidayKS*, LA, MI, NC, ND, OK, SC, TX, VA*, WI
Dec 24SaturdayChristmas EveObservance, Christian
Dec 24SaturdayChristmas EveState holidayKS*, LA, MI, NC, ND, OK, SC, TX, VA*, WI
Dec 25SundayChanukah/Hanukkah (first day)Jewish holiday
Dec 25SundayChristmas DayFederal Holiday
Dec 26MondayKwanzaa (until Jan 1)Observance
Dec 26MondayChristmas Day observedFederal Holiday
Dec 26MondayDay After Christmas DayState holidayKS, KY, NC, NC, NH, SC, TX
Dec 30FridayNew Year's Eve observedState holidayLA, MI, WI
Dec 31SaturdayNew Year's EveObservance
Dec 31SaturdayNew Year's EveState holidayLA, 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