A Simple Calendar Database you can Download
I chose to create a calendar which began on January 1, 2009 and extended through June 6, 2031. I wrote a SQL script to generate the business day and holiday data after I realized that computing it real-time was not an optimal solution. My calendar ended up with more than 8,000 dates and almost 2,500 holidays. I’m glad I didn’t try to do that by hand. You can check out an application based on this database.
Most of the script code which originally calculated the holidays is mine, although a lot of it is derived from the work of people too numerous to mention. By the way, since the original calendar table is generated by a script, changing a couple of numbers allows me to create a calendar with many more or many fewer dates, or a different starting date. I can also add, edit, or remove holidays in the script. Anyone can manually edit holidays individually in the finished table.
The script I have made available simply recreates the database and the three finished calendar tables including data. Any changes to the data will need to be made manually. The default name of the database is Calendar1. If you want the tables added to another database change Calendar1 to the name of your database in the script. If your database already exists, edit the first line of the script that points to (Uses) Calendar1.
If you wish me to create a custom calendar table, please contact me for a quote.
This SQL script will recreate the database and tables (Calendar, DayOfWeek, MonthOfYear 463 KB ZIP), including the data, on SQL Server 2008+. There are also four default constraints and two foreign key constraints plus some bonus tables. Here is a breakdown of the schema.
The following list is the columns in the table.
- CalendarDate
- DayID
- DayOfYearID
- WeekOfYearID
- MonthOfYearID
- QuarterOfYearID
- YearID
- DayOfWeekID
- DayOfMonthID
- WeekOfMonthID
- isBusinessDay
- isHoliday
- IsLegalHoliday
- HolidayName
- HolidayNote
- CreatedDate
Any column whose name ends in ID is a smallint number for that item. This is Beta version 0.9, so help me out by looking for problems.
The 2011 holidays:
| Calendar Date | Day | Holiday Name | Holiday Note |
| 2011-01-01 | Saturday, January 1 | New Year's Day | New Year's Day |
| 2011-01-06 | Thursday, January 6 | Día de Los Santos Reyes | The day of the three wise men, presents given |
| 2011-01-17 | Monday, January 17 | Martin Luther King Day (US) | 3rd Monday in January |
| 2011-02-02 | Wednesday, February 2 | Groundhog Day (US), Día de la Candelaria (Mexico) | February 2 |
| 2011-02-12 | Saturday, February 12 | Lincoln's Birthday (US) | February 12 |
| 2011-02-14 | Monday, February 14 | St. Valentines Day | February 14 |
| 2011-02-15 | Tuesday, February 15 | Mawlid al-Nabi the Muslim Prophet’s Birthday | According to the Umm al-Qura calendar |
| 2011-02-21 | Monday, February 21 | Presidents Day (observed) (US) | 3rd Monday in February |
| 2011-03-08 | Tuesday, March 8 | Fat Tuesday (Mardi Gras) | Carnival; day before Lent starts |
| 2011-03-09 | Wednesday, March 9 | Ash Wednesday | First day of Lent |
| 2011-03-17 | Thursday, March 17 | St. Patrick's Day | March 17 |
| 2011-04-01 | Friday, April 1 | April Fool's Day | April 1 |
| 2011-04-15 | Friday, April 15 | Income Tax Day (US) | April 15, April 17 if the 15th falls on a Sunday |
| 2011-04-16 | Saturday, April 16 | Emancipation Day (US) | April 16 |
| 2011-04-19 | Tuesday, April 19 | Pesach (Passover) | Jewish holiday commemorating the Exodus from Egypt |
| 2011-04-21 | Thursday, April 21 | Baha'i Festival of Ridvan begins | Commemorates the 12 days (April 21-May 2, 1863) when Baha'u'llah, the founder of the Baha'i Faith, publicly proclaimed His mission as God's messenger for this age |
| 2011-04-22 | Friday, April 22 | Good Friday | The Friday before Easter |
| 2011-04-24 | Sunday, April 24 | Easter | Christian celebration of rebirth |
| 2011-05-01 | Sunday, May 1 | Labor Day (Mexico) | Primero de Mayo is the Mexican national holiday that is equivalent to the U.S. Labor Day. |
| 2011-05-05 | Thursday, May 5 | Cinco de Mayo | May 5, commemorates the Mexican army's victory over French forces at the Battle of Puebla on May 5, 1862 |
| 2011-05-08 | Sunday, May 8 | Mother's Day (US) | 2nd Sunday in May |
| 2011-05-23 | Monday, May 23 | Victoria Day (Canada) | Monday on or before May 24; the birthday of Queen Victoria, the sovereign at the time of Canadian Confederation and establishment of Dominion status in 1867 |
| 2011-05-29 | Sunday, May 29 | Ascension of Bahá'u'lláh | a Holy Day for six million Baha'is in 192 countries and territories |
| 2011-05-30 | Monday, May 30 | Memorial Day (observed) (US) | Last Monday in May |
| 2011-06-14 | Tuesday, June 14 | Flag Day (US) | June 14 |
| 2011-06-19 | Sunday, June 19 | Father's Day (US) | 3rd Sunday in June |
| 2011-07-01 | Friday, July 1 | Canada Day | Celebrates Canada's 1867 Confederation and establishment of dominion status. |
| 2011-07-04 | Monday, July 4 | Independence Day (US) | July 4 |
| 2011-07-09 | Saturday, July 9 | Baha'i: Martyrdom of the Bab | The anniversary of the execution of the Bab (Siyyid 'Ali-Muhammad), the herald of the Baha'i Faith |
| 2011-08-01 | Monday, August 1 | Begin Ramadan, Muslim month of fasting | Begin the Muslim month of fasting according to the Umm al-Qura calendar |
| 2011-08-30 | Tuesday, August 30 | Id al-Fitr end of Ramadan | End of the Muslim month of fasting according to the Umm al-Qura calendar |
| 2011-09-05 | Monday, September 5 | Labor Day (US & Canada) | 1st Monday in September |
| 2011-09-16 | Friday, September 16 | Día de Independencia (Mexico) | Celebrates the day that Miguel Hidalgo delivered El Grito de Dolores beginning the War of Independance. |
| 2011-09-23 | Friday, September 23 | Native American Day (US) | 4th Friday in September |
| 2011-10-10 | Monday, October 10 | Columbus Day (observed) (US); Thanksgiving (Canada) | 2nd Monday in October |
| 2011-10-31 | Monday, October 31 | Halloween (US) | October 31 |
| 2011-11-01 | Tuesday, November 1 | Día de los Muertos (Mexico) | November 1 and 2; two days to honor Mexico's dead |
| 2011-11-02 | Wednesday, November 2 | Día de los Muertos (Mexico) | November 1 and 2; two days to honor Mexico's dead |
| 2011-11-06 | Sunday, November 6 | Id al-Adha Muslim Feast of Sacrifice | Id al-Adha Feast of Sacrifice, which ends the period of the annual pilgrimage (Hajj) according to the Umm al-Qura calendar |
| 2011-11-11 | Friday, November 11 | Veteran's Day (US); Remembrance Day (Canada) | November 11 |
| 2011-11-24 | Thursday, November 24 | Thanksgiving Day (US) | 4th Thursday in November |
| 2011-11-26 | Saturday, November 26 | Ras al-Sana, Muslim New Year | According to the Umm al-Qura calendar |
| 2011-12-12 | Monday, December 12 | Nuestra Señora de Guadalupe | Our Lady of Guadalupe, Mexico, December 12 |
| 2011-12-21 | Wednesday, December 21 | Chanukah Begins | Jewish Festival of Rededication, also known as the Festival of Lights |
| 2011-12-25 | Sunday, December 25 | Christmas Day (English), Navidad (Español), Noël (French) | December 25 |
| 2011-12-26 | Monday, December 26 | Christmas Legal Holiday; Kwanzaa begins (US); Boxing Day (Canada) | Christmas Day legal holiday is Monday |
Pre-calculating all of this information for each date simplifies all kinds of date calculations and makes it possible to access calendar data through normal data access code and business logic code.
The calendar includes secular, Christian, Jewish, Baha'i, and Muslim holidays. The major Islamic holidays are not computed but are determined by astronomic observation each year. The vagaries of visual observation can cause the date of a holy day to vary by a day from the date in this calendar.
All of Conrad Muller's work on this page is experimental and is free for you to use, but no warrenty is stated or implied.