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 provides 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 has any support for public holidays or an algorithm of some sort to work them out? Or does anyone have somewhere official that I can bulk insert from.

Answer 1

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

---

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 2

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 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

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 6

You can user the Nager.Date library for calculate public holidays. https://github.com/nager/Nager.Date

Nuget

PM> install-package Nager.Date

Example:

var publicHolidays = DateSystem.GetPublicHoliday(CountryCode.GB, 2017);
foreach (var publicHoliday in publicHolidays)
{
    var name = publicHoliday.LocalName;
}

Answer 7

For many of us in the USA, there is a standard list of business holidays which can be deterministically calculated.

Drawing inspiration from Vonpato's code, I put together a single SQL statement that makes use of Window functions ( over(partition by ..) ), new in SQL Server 2008, to determine the N'th or last occurrence of a weekday within a month.

This should be run against "DimDate", a standard Date Dimension table such as found in a Data Warehouse. http://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho

SELECT 
    case 
        when DayNameOfWeek = 'Saturday' then dateadd(day, -1, FullDate) 
        when DayNameOfWeek = 'Sunday' then dateadd(day, 1, FullDate) 
    else FullDate end as 'Holiday Date'
FROM (
    select row_number() over (partition by DayNameOfWeek, MonthOfYear, CalendarYear order by FullDate) as WeekdayOrdinal,
    count(*) over (partition by DayNameOfWeek, MonthOfYear, CalendarYear ) as MaxOrdinal,    *  
    from DimDate  
) D
WHERE
        (D.[MonthName] = 'January'  and [DayOfMonth]  = 1)                                              /* New Years Day    */
    OR  (D.[MonthName] = 'January'  and DayNameOfWeek = 'Monday')   and WeekdayOrdinal = 3              /* MLK Day          */
    OR  (D.[MonthName] = 'February' and DayNameOfWeek = 'Monday')   and WeekdayOrdinal = 3              /* President's Day  */
    OR  (D.[MonthName] = 'May'      and DayNameOfWeek = 'Monday')   and WeekdayOrdinal = MaxOrdinal     /* Memorial Day     */
    OR  (D.[MonthName] = 'September' and DayNameOfWeek = 'Monday')  and WeekdayOrdinal = 1              /* Labor Day        */
    OR  (D.[MonthName] = 'October'  and DayNameOfWeek = 'Monday')   and WeekdayOrdinal = 2              /* Columbus Day     */
    OR  (D.[MonthName] = 'November' and [DayOfMonth] = 11)                                              /* Veteran's Day    */
    OR  (D.[MonthName] = 'November' and DayNameOfWeek = 'Thursday') and WeekdayOrdinal = 4              /* Thanksgiving     */
    OR  (D.[MonthName] = 'December' and [DayOfMonth]  = 25 )                                            /* Christmas        */
ORDER BY FullDate

Answer 8

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 9

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 10

Let's simplify this:

---

case

-- New Year's Day
when DATEPART(MM, @DATE) = 1
    and DATEPART(DD, @DATE) = 1
    and DATEPART(DW, @DATE) in (2,3,4,5,6) then 'Y'
when DATEPART(MM, @DATE) = 12
    and DATEPART(DD, @DATE) = 31
    and DATEPART(DW, @DATE) = 6 then 'Y'
when DATEPART(MM, @DATE) = 1
    and DATEPART(DD, @DATE) = 2
    and DATEPART(DW, @DATE) = 2 then 'Y'

-- Memorial Day (last Monday in May)
when DATEPART(MM, @DATE) = 5
    and DATEPART(DD, @DATE) between 25 and 31
    and DATEPART(DW, @DATE) = 2 then 'Y'

-- Independence Day
when DATEPART(MM, @DATE) = 7
    and DATEPART(DD, @DATE) = 4
    and DATEPART(DW, @DATE) in (2,3,4,5,6) then 'Y'
when DATEPART(MM, @DATE) = 7
    and DATEPART(DD, @DATE) = 3
    and DATEPART(DW, @DATE) = 6 then 'Y'
when DATEPART(MM, @DATE) = 7
    and DATEPART(DD, @DATE) = 5
    and DATEPART(DW, @DATE) = 2 then 'Y'

-- Labor Day (first Monday in September)
when DATEPART(MM, @DATE) = 9
    and DATEPART(DD, @DATE) between 1 and 7
    and DATEPART(DW, @DATE) = 2 then 'Y'

-- Thanksgiving Day (fourth Thursday in November)
when DATEPART(MM, @DATE) = 11
    and DATEPART(DD, @DATE) between 22 and 28
    and DATEPART(DW, @DATE) = 5 then 'Y'

-- Black Friday (day after Thanksgiving)
when DATEPART(MM, @DATE) = 11
    and DATEPART(DD, @DATE) between 23 and 29
    and DATEPART(DW, @DATE) = 6 then 'Y'

-- Christmas Day
when DATEPART(MM, @DATE) = 12
    and DATEPART(DD, @DATE) = 25
    and DATEPART(DW, @DATE) in (2,3,4,5,6) then 'Y'
when DATEPART(MM, @DATE) = 12
    and DATEPART(DD, @DATE) = 24
    and DATEPART(DW, @DATE) = 6 then 'Y'
when DATEPART(MM, @DATE) = 12
    and DATEPART(DD, @DATE) = 26
    and DATEPART(DW, @DATE) = 2 then 'Y'

else 'N' end

Answer 11

For those having trouble finding the last Thursday of November for Thanksgiving, I start by finding the last day of the month then use a WHILE() loop to minus a day and check the datename until the datename is Thursday:

DECLARE @LastThursday DateTime = DATEADD(s,-1,DATEADD(mm,DATEDIFF(m,0,CONVERT(datetime,CONVERT(varchar,YEAR(GETDATE()))+'-11-01'))+1,0))

WHILE DATENAME(weekday,@LastThursday) <> 'Thursday'
BEGIN
    SET @LastThursday = DATEADD(day,DATEDIFF(day,0,@LastThursday)-1,0)
END

SELECT @LastThursday

Answer 12

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 13

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 14

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

Answer 15

In some countries (i.e. in Poland) number of holidays deepens on Easter.

At the end of this post you can find a code to calculate eater date and few holidays that depends on it.

Non-movable holidays, that are i.e. January 1st, Christmas Holidays, Independence day etc. can be stored somewhere in table.

Bear in mind that in number countries you have regions/states that can have additional public holidays, but to calculate all holidays for your country or region should be possible.

In UK i.e. you have Bank holidays that have some rules you can use in your code. - First Monday of May - Last Monday of May - Last Monday of August etc.

Please check this link for more details http://en.wikipedia.org/wiki/Public_holidays_in_the_United_Kingdom

How to calculate Easter date in SQL:

http://www.smart.net/~mmontes/nature1876.html

http://ghiorzi.org/easterda.htm

declare @a int
declare @b int
declare @c int
declare @d int
declare @e int
declare @f int
declare @g int
declare @h int
declare @i int
declare @j int
declare @k int
declare @l int
declare @m int
declare @n int
declare @Year int
declare @Month int
declare @Day int
declare @EasterSunday datetime
declare @EasterMonday datetime
declare @Pentecost datetime
declare @CorpusChristi datetime

SET @Year = 2014

SET @a = @Year%19;
SET @b = @Year/100;
SET @c = @Year%100;
SET @d = @b/4;
SET @e = @b%4;
SET @f = @c/4;
SET @g = @c%4;


   SET @h = (@b + 8)/25;
   SET @i = (@b - @h + 1)/3;
   SET @j = (19*@a + @b -  @d - @i + 15) % 30;
   SET @k = (32 + 2*@e + 2*@f - @j - @g) % 7;
   SET @m = (@a + 11*@j + 22*@k) / 451;
   SET @n = @j + @k - 7*@m + 114;

   SET @Month = @n/31;
   SET @Day = (@n % 31) + 1;

--PRINT @Year
--PRINT @Month
--PRINT @Day

SET @EasterSunday = dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
SET @EasterMonday = dateadd(day,1,@EasterSunday)
SET @Pentecost = dateadd(day,49,@EasterSunday)
SET @CorpusChristi = dateadd(day,60,@EasterSunday)


PRINT 'Easter Sunday: ' + CONVERT(VARCHAR, @EasterSunday,120) + ' [' + DATENAME(dw, @EasterSunday) + ']'
PRINT ''

PRINT 'Easter Monday: ' + CONVERT(VARCHAR, @EasterMonday,120) + ' [' + DATENAME(dw, @EasterMonday) + ']'
PRINT ''

PRINT 'Pentecost: ' + CONVERT(VARCHAR, @Pentecost,120) + ' [' + DATENAME(dw, @Pentecost) + ']'
PRINT ''

PRINT 'CorpusChristi: ' + CONVERT(VARCHAR, @CorpusChristi,120) + ' [' + DATENAME(dw, @CorpusChristi) + ']'
PRINT ''

Answer 16

Bank Holidays in the UK are mostly computable, but definitely not all computable.

The UK government provides an API (https://www.gov.uk/bank-holidays.json) which gives you this info.

Answer 17

I find this topic strange. After trying to find and write complicated functions in MS, PHP or in this case SQL language I came to the conclusion that it is a pointless exercise. The code I ended up contain over 2000 characters and takes hours to get right and debug. If you think about it all you have to do is write 7 number indicating the day 1-365 for each holiday and a row for each year for the next 100 years. You only need to load that years or the next years holidays once. So keep table with 7 columns and 100 rows and fill them up with the correct days. You only need to do this once every 100 years and take less than an hour and it is portable.

Answer 18

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 19

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 20

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

Answer 21

I tried the "answer" and spent time to heavily modify it to be more readable. Really those "-DATEPART" options aren't needed since we are setting the date right before. It worked pretty well but was having issues with MLK day 2020 so my trust in it was shaky.

Additionally I didnt like the fact that it was very difficult to read. I found this and it's much easier to get through. It works very similar to a date dimension table and tested accurately for the 3 years I tried.

https://www.codeproject.com/Tips/1168430/US-Federal-Holidays-SQL

Answer 22

See this answer, it was very easy to implement Joe Booth's solution at http://www.joebooth-consulting.com/sqlServer/sqlServer.html#CalendFunc.

I did need to adjust the Holiday_List function by adding Memorial day after Armed Forces Day, like so:

IF @nYear>=1868 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Memorial Day',dbo.FloatingDate(0,2,6,@nYear)) -- Last Monday in May 

I also came up with this case statement to adjust the day to be the previous or following business day if the holiday falls on a weekend:

case 
    when DATENAME(WEEKDAY,Holiday_date)='Saturday' 
        then dateadd(d,-1,Holiday_date) 
    when DATENAME(WEEKDAY,Holiday_date)='Sunday' 
        then dateadd(d,1,Holiday_date) 
    else
        Holiday_date
    end as adjusted

Answer 23

I took Vonpota's answer and made it a Table Valued Function so I could use a NOT IN clause.

CREATE FUNCTION [udf_Holidays](@Year char(4))
    RETURNS @Holidays TABLE (
        HolidayName NVARCHAR(30),
        HolidayDate DATETIME,
        [DayOfWeek] NVARCHAR(15)
    )
AS
BEGIN
DECLARE @Date datetime
DECLARE @Holiday datetime



---- 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
INSERT INTO @Holidays
SELECT  'New Years Day', @Date, 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
INSERT INTO @Holidays
SELECT 'Martin L King’s Birthday', @Holiday , 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
INSERT INTO @Holidays
SELECT 'President’s Day', @Holiday , 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
INSERT INTO @Holidays
SELECT 'Memorial Day', @Holiday , 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
INSERT INTO @Holidays    
SELECT 'Independence Day', @Date , 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 )
INSERT INTO @Holidays-- 1st Monday of the Month
SELECT 'Labor Day', @Holiday, 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
INSERT INTO @Holidays
SELECT  'Columbus Day', @Holiday, 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
INSERT INTO @Holidays
SELECT 'Veteran’s Day', @Date , 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
INSERT INTO @Holidays
SELECT 'Thanksgiving Day', @Holiday , 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
INSERT INTO @Holidays    
SELECT 'Christmas Day', @Date , DATENAME( dw, @Date ) --'DayOfWeek'


    RETURN;
END;

Answer 24

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

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

Answer 25

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,;
}