DataBaseZone.com
Conrad Muller
Juneau, Alaska

Email: conrad at
databasezone
dot com

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.

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.

Home | Resume | Project Portfolio | Writings | Developer Resources | Contact Form