How do I determine a public holiday in Sql server?

Question

I have an application written in c# that cannot run on a public holiday or a weekend. I've looked around a bit and haven't found anywhere (official) that provide all the public holidays for the next say 50 years.

If I can get these, I will simply bulk insert them into my sql server Holidays table and use that. However, I can't find this data anywhere.

Does anyone know if Sql server have any support for public holidays or an algorithm of some sort to work them out? Or anyone got somewhere official that I can bulk insert from.

Thanks.

EDIT: For arguments sake lets just assume we want public holidays in England!!!!

Answer 1

Isn't a public holiday very dependent of locale?

Programatically, there is no way to compute this. Alternatively, you will need to find an official list of holidays for each of your locales. And even so, you will not get the granularity you want.

Answer 2

Public holidays are rarely available for the next 50 years. In some countries they are known for the next couple of years, in some countries not. They also vary by states and even cities.

Answer 3

As far as I know there isn't any 'official' source, and this is indeed highly dependent on locale. You'd have to manage these manually. A good source seems to be:

http://en.wikipedia.org/wiki/List_of_holidays_by_country

Even under these circumstances it's very tricky to manage. For example, I live in Belgium, and different parts of the country have different holidays. Government workers for example have different holidays compared to the rest of the workforce, and this is in the same locale code.

Answer 4

In our applications we have it part of the user configuration. There is a place for users to set what they consider public/bank holidays for as far into the future as they want. For ease of use there is the ability to copy them by date from previous years. This also allows them to set 'custom' public holdays, perhaps a day that the company treats as a holiday but is not natioanlly official...

Programatically, however, there are absolutely no assumptions at all. It's effectively just a user maintained table of dates.

(This includes the UK because, as stated above, some holidays vary and are not set in stone, and sometimes there are special one-off days.)

Answer 5

Ahhh, holiday calendars. The bane of any investment bank programmer's life. There's no way of doing it other than maintaining your own list, I'm afraid!

Answer 6

In addition to allowing the user to configure what days are holidays, it would be nice if you allowed the user to select a calendar to import or even to subscribe to. iCalShare has a nice list. However, it's probably too much work for a feature that's merely nice.

Answer 7

To add what other people have already said, putting your own in a table is the only real way of doing it. A classic example is the extra bank holiday for the Queen's golden jubilee. This was only announced a couple of years in advance, and there was no way you could have it fifty years in advance.

Because of this a number of commercial services exist to provide this data in a reliable way. One is GoodBusinessDay.com but I have no experience with it.

Answer 8

There is a web service available for this --

http://www.holidaywebservice.com/Holidays/servicesAvailable_HolidayService.aspx

Answer 9

If it's just England, then you can work them out for yourself! You'll need to get a reliable algorithm for determining Easter, but otherwise I'd say you could do it in under an hour.

But do you mean just England, or the UK? Because Scotland has different holidays (Christmas, Hogmanay and St Andrew's Day) and Northern Ireland, Wales and most likely the Isle of Man and the Channel Islands should also be traded differently.

As noted elsewhere, once your scope gets wider then it's even more complex. There are local holidays, half-days, days when banks are open but stock exchanges not, all kinds of horrors.

If you really can't manage holidays yourself and don't have users who can be given responsibility, then I'd suggest going back to your "can't run on a public holiday" constraint and looking for ways in which that might be removed...

Answer 10

google calendar public hollydays Maybe you could retrive your country/region data with google Calendar web service.

Answer 11

You are going to need to maintain a holiday table for this. For proper internationalization, you even need to maintain which days are weekend days for this since even that is not universal. Likewise, you might need to maintain holidays by locale so your program knows the users in London have the day off but the users in Turkey do not.

This should be completely configurable by the user. For all you know, the company owner's birthday might be a "standard" day off. Try to find that holiday on the web.

Lastly, you do not want to store 50 years worth of holiday data. It will only be inaccurate and potentially slow all your code down.

Answer 12

Found another service for worldwide public holidays, allegedly used by calendar publishers:

http://www.qppstudio.net/index.htm

Answer 13

If it helps I have a text file that lists the rules for calculating the banking holidays defined for all the major and minor financial centers. The usual way of defining this is by business center, as in London, New York Frankfurt etc. etc, rather than by country. As an example here are the rules and explanations for London and a few others. If any one wants the whole lot let me know. This lot should work from 1990 until 2030 at the earliest. Obviously it won't have any new holidays announced after the fles was put together (2006 I think).

/* * This file describes holiday calendars in the following way: * 1. The "n"th "dow" in the month "m", means the 3rd Wednesday in August = "ALWAYS,3,WED,AUG" * 2. The last etc "dow" in month "m", e.g. the last Monday in June = "LAST,MON,JUN" * 3. A set date which if on a Sat or Sun is taken on the Monday, eg 13th Aug. = "FWDFROM,13,AUG" * 4. A set date which if on a Sat is taken on Fri, if Sun on the Mon = "CLOSESTTO,13,AUG" * 5. A set date which, if on a Sat is unchanged, but if a Sun is taken on the Mon = "FWDFROMSUN,13,AUG" * 6. A set date which is unchanged if it happens to be on a weekend = "REMAINS,13,AUG" * 7. The first day on or after day "x" in month "m" e.g. 1st Monday on or after 16th Jan = "FIRSTAFTER,16,MON,JAN" * 8. A holiday which is a set number of days relative to Easter Sunday, e.g Easter Monday = "EASTER,1" * 9. A holiday on one specified date only. e.g. 13th Aug 1997 = "ONEOFF,13,AUG,1997" * 10.A specific date which has been added but is not a holiday and must be removed. = "REMOVE,13,AUG,1997" * Note REMOVE only works on a date which is already in the calendar: you cannot remove a date which is yet to be added. * If there is any text after the definition of the holiday it will appear in the date calendar description, * e.g. you can have "FWDFROM,25,DEC,Christmas Day", if you wish. */

LON: HolidayCalendar {
Config: String { FWDFROM,1,JAN,New Year's Day; EASTER,-2,Good Friday; EASTER,1,Easter Monday; ALWAYS,1,MON,MAY,Early May Bank Holiday; LAST,MON,MAY,Spring Bank Holiday; REMOVE,27,MAY,2002,Spring Bank Holiday; ONEOFF,3,JUN,2002,Spring Bank Holiday; ONEOFF,4,JUN,2002,Spring Bank Holiday; LAST,MON,AUG,Summer Bank Holiday; FWDFROM,25,DEC,Christmas; FWDFROM,26,DEC,Boxing Day; ONEOFF,31,DEC,1999,Millenium; } EndDate: 31-Dec-2030; Name: London; StartDate: 01-Jan-1990; Weekend: SAT,SUN,; }

HKG: HolidayCalendar {
Config: String {
FWDFROM,1,JAN,New Year's Day; ONEOFF,16,FEB,1999,Chinese New Year;
ONEOFF,17,FEB,1999,Chinese New Year;
ONEOFF,18,FEB,1999,Chinese New Year;
FWDFROM,5,FEB,2000,Chinese New Year;
FWDFROM,6,FEB,2000,Chinese New Year;
FWDFROM,7,FEB,2000,Chinese New Year;
CLOSESTTO,5,APR,Ching Ming;
EASTER,-2,Good Friday; EASTER,1,Easter Monday;
FWDFROM,1,MAY,Labour Day;
ONEOFF,18,JUN,1999,Dragon Boat Festival;
FWDFROM,1,JUL,SAR Establishment Day;
FWDFROM,1,OCT,National Day; FWDFROM,2,OCT,National Day; ONEOFF,25,SEP,1999,Mid Autumn Festival; ONEOFF,17,OCT,1999,Chung Yeung; FWDFROM,25,DEC,Christmas; FWDFROM,26,DEC,Boxing Day; ONEOFF,4,MAY,1998,May bank holiday; ONEOFF,25,MAY,1998,May bank holiday; ONEOFF,3,MAY,1999,May bank holiday; ONEOFF,31,MAY,1999,May bank holiday; ONEOFF,1,JUL,1997; ONEOFF,2,JUL,1997; } EndDate: 31-Dec-2030; Name: Hong Kong; StartDate: 01-Jan-1990; Weekend: SAT,SUN,; }

MIL: HolidayCalendar { Config: String { FWDFROM,1,JAN,New Year's Day; REMAINS,6,JAN,Epiphany; REMAINS,25,APR,Liberation Day; REMAINS,1,MAY,May Day; REMAINS,15,AUG,Assumption; REMAINS,1,NOV,All Saint's; REMAINS,8,DEC,Immaculate Conception; EASTER,1,Easter Monday; FWDFROM,25,DEC,Christmas; FWDFROM,26,DEC,Boxing Day; } EndDate: 31-Dec-2030; Name: Milan; StartDate: 01-Jan-1990; Weekend: SAT,SUN,; }

FFT: HolidayCalendar { Config: String { REMAINS,1,JAN,New Year's Day; EASTER,-2,Good Friday; EASTER,1,Easter Monday; REMAINS,1,MAY,Labour Day; EASTER,39,Ascension Day; EASTER,50,Whit Monday; EASTER,60,Corpus Christi; REMAINS,3,OCT,Day of German Unity; REMAINS,24,DEC,Christmas Eve; REMAINS,25,DEC,Christmas Day; REMAINS,26,DEC,Boxing Day; } EndDate: 31-Dec-2030; Name: Frankfurt; StartDate: 01-Jan-1990; Weekend: SAT,SUN,; }

ZUR: HolidayCalendar { Config: String { REMAINS,1,JAN,New Year's Day; REMAINS,2,JAN,New Year's Holiday; EASTER,-2,Good Friday; EASTER,1,Easter Monday; EASTER,39,Ascension Day; EASTER,50,Whit Monday; REMAINS,1,AUG,August Bank Holiday; REMAINS,1,MAY, LABOUR DAY; REMAINS,25,DEC,Christmas; REMAINS,26,DEC,Boxing Day; } EndDate: 31-Dec-2030; Name: Zurich; StartDate: 01-Jan-1990; Weekend: SAT,SUN,; }

NYK: HolidayCalendar { Config: String { REMAINS,1,JAN,New Year's Day; ALWAYS,3,MON,JAN,Martin Luther King; ALWAYS,3,MON,FEB,President's Day; LAST,MON,MAY,Memorial Day; CLOSESTTO,4,JUL,Independence Day; ALWAYS,1,MON,SEP,Labor Day; ALWAYS,2,MON,OCT,Columbus Day; REMAINS,11,NOV,Veteran's Day; ALWAYS,4,THU,NOV,Thanksgiving; FWDFROMSUN,25,DEC,Christmas Day; ONEOFF,12,NOV,2001,Veteran's Day; } EndDate: 31-Dec-2030; Name: New York; StartDate: 01-Jan-1990; Weekend: SAT,SUN,; }

Answer 14

This algorithm may reduce the amount of manual configuration in countries that observe Christian holidays. Should be trivial to convert to C#:

http://en.wikipedia.org/wiki/Computus#Anonymous_Gregorian_algorithm

Answer 15

Hi:

I just gather information over the internet and I come with this easy way to calculate the US Bank Holidays.

I use the information in this site:

http://www.buyusa.gov/uk/en/us_bank_holidays.html

---

US Bank Holidays

===========================

DECLARE @Year char(4)
, @Date datetime
, @Holiday datetime

SET @Year = 2010

---- New Years Day
SET @Date=CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-01-01' ) 
IF DATENAME( dw, @Date ) = 'Saturday'
 SET @Date=@Date-1
ELSE IF DATENAME( dw, @Date ) = 'Sunday'
 SET @Date=@Date+1
SELECT @Date [New Years Day], DATENAME( dw, @Date ) [DayOfWeek]

---- Martin L King's Birthday ( 3rd Monday in January )
SET @Date = CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-01-01' ) 
SET @Holiday = DATEADD( wk, DATEDIFF( wk, 0, dateadd( dd, 18-datepart( day, @Date ), @Date ) ), 0 ) -- 3rd Monday of the Month
SELECT @Holiday [Martin L King's Birthday], DATENAME( dw, @Holiday ) [DayOfWeek]

---- President’s Day ( 3rd Monday in February )
SET @Date = CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-02-01' ) 
SET @Holiday = DATEADD( wk, DATEDIFF( wk, 0, dateadd( dd, 18-datepart( day, @Date ), @Date ) ), 0 ) -- 3rd Monday of the Month
SELECT @Holiday [President’s Day], DATENAME( dw, @Holiday ) [DayOfWeek]

---- Memorial Day ( Last Monday in May )
SET @Date = CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-05-01' ) 
SET @Holiday = DATEADD( wk, DATEDIFF( wk, 0, dateadd( dd, 30-datepart( day, @Date ), @Date ) ), 0 ) -- 5th Monday of the Month
SELECT @Holiday [Memorial Day], DATENAME( dw, @Holiday ) [DayOfWeek]

---- Independence Day ( July 4 )
SET @Date=CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-07-04' ) 
IF DATENAME( dw, @Date ) = 'Saturday'
 SET @Date=@Date-1
ELSE IF DATENAME( dw, @Date ) = 'Sunday'
 SET @Date=@Date+1
SELECT @Date [Independence Day], DATENAME( dw, @Date ) [DayOfWeek]

---- Labor Day ( 1st Monday in September )
SET @Date = CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-09-01' ) 
SET @Holiday = DATEADD( wk, DATEDIFF( wk, 0, dateadd( dd, 6-datepart( day, @Date ), @Date ) ), 0 ) -- 1st Monday of the Month
SELECT @Holiday [Labor Day], DATENAME( dw, @Holiday ) [DayOfWeek]

---- Columbus Day ( 2nd Monday in October )
SET @Date = CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-10-01' ) 
SET @Holiday = DATEADD( wk, DATEDIFF( wk, 0, dateadd( dd, 12-datepart( day, @Date ), @Date ) ), 0 ) -- 2nd Monday of the Month
SELECT @Holiday [Columbus Day], DATENAME( dw, @Holiday ) [DayOfWeek]

---- Veteran’s Day ( November 11 )
SET @Date=CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-11-11' ) 
IF DATENAME( dw, @Date ) = 'Saturday'
 SET @Date=@Date-1
ELSE IF DATENAME( dw, @Date ) = 'Sunday'
 SET @Date=@Date+1
SELECT @Date [Veteran’s Day], DATENAME( dw, @Date ) [DayOfWeek]

---- Thanksgiving Day ( 4th Thursday in November )
SET @Date = CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-11-04' ) 
SET @Holiday = DATEADD( wk, DATEDIFF( wk, 0, dateadd( dd, 22-datepart( day, @Date ), @Date ) ), 0 )+3 -- 4th Thursday of the Month
SELECT @Holiday [Thanksgiving Day], DATENAME( dw, @Holiday ) [DayOfWeek]

---- Christmas Day ( December 25 )
SET @Date=CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-12-25' ) 
IF DATENAME( dw, @Date ) = 'Saturday'
 SET @Date=@Date-1
ELSE IF DATENAME( dw, @Date ) = 'Sunday'
 SET @Date=@Date+1
SELECT @Date [Christmas Day], DATENAME( dw, @Date ) [DayOfWeek]

---- New Years Eve Day
SET @Date=CONVERT( datetime, CONVERT(varchar, YEAR( @Year ) )+'-12-31' ) 
IF DATENAME( dw, @Date ) = 'Saturday'
 SET @Date=@Date-1
ELSE IF DATENAME( dw, @Date ) = 'Sunday'
 SET @Date=@Date+1
SELECT @Date [New Years Day], DATENAME( dw, @Date ) [DayOfWeek]

Answer 16

Financial institutions will subscribe to services which provide periodic updates to banking calendars for various locales.

Note that there are often differences between banking holidays and ordinary holidays. An example of this in the United States is this passed Independence Day where the federal government recognized the holiday on July 3rd but the Federal Reserve Bank of New York was open.

2009 U.S. Federal Holidays

2009 U.S. Federal Reserve Bank Holidays