calculating calendar holidays

I want to add US holidays to a calendar from events stored in database

Some examples:
Mothers Day = 2nd Sunday in May
Fathers Day = 3rd Sunday in June
Thanksgiving = 4th Thursday in November - Not the last Thursday cause it may have 5 some years

I need ideas on what is best solution to calculate
LVL 25
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
saw a function on internet, you probably can adapt this
CREATE FUNCTION dbo.fnGetNthWeekdayOfMonth
    @theDate DATETIME,
    @theWeekday TINYINT,
    @theNth SMALLINT
    RETURN  (
                SELECT theDate
                FROM    (
                            SELECT  DATEADD(DAY, 7 * @theNth - 7 * SIGN(SIGN(@theNth) + 1) +(@theWeekday + 6 - DATEDIFF(DAY, '17530101', DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')) % 7) % 7, DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')) AS theDate
                            WHERE   @theWeekday BETWEEN 1 AND 7
                                    AND @theNth IN (-5, -4, -3, -2, -1, 1, 2, 3, 4, 5)
                        ) AS d
                WHERE   DATEDIFF(MONTH, theDate, @theDate) = 0

Open in new window

-- Mothers Day
select dbo.fnGetNthWeekdayOfMonth('1 May 2015', 7, 2);

-- Fathers Day
select dbo.fnGetNthWeekdayOfMonth('1 Jun 2015', 7, 3);

-- Thanksgiving
select dbo.fnGetNthWeekdayOfMonth('1 Nov 2015', 4, 4);

Open in new window

original link:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Data DudeCommented:
Here's an article I wrote that spells out how to account for every type of holiday:  SQL Server Calendar Table.    Scroll down a little to see all the functions themselves, and the article demonstrates how to build a table of days, where those functions are executed to note which days are holidays, so that the table can be used for querying.

Good luck.
Be careful with functions. They slow down query performance. I'd go with @Jim table.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulEE Topic AdvisorCommented:
The function above is suited to holidays that are established by "nth day of month" only, not all holidays follow that type of rule.

I also suggest a table for your holidays. You can do that in 2 ways

a. like Jim's a calendar table of all days for a broad range, with attribute to indicate if a holiday
b. just a row for each holiday (i.e a smaller table)

Personally I find the calendar table (option a) easiest to work with.
dgrafxAuthor Commented:
How would a person go about querying for holidays in a setup like Jims or a smaller such table?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
the intention to use function in this scenario is to find the specific date of a holiday with a predetermined pattern, of course when we are doing a more complex query, the function will slow down the overall performance, and as other experts suggested, to store those holidays into a table for references could be a better approach if we are knowing those specific holidays at the first place.

>>How would a person go about querying for holidays in a setup like Jims or a smaller such table?
just treat the holidays are stored in a table, so we can use a Select SQL statement to pull out the relevant records accordingly but this depends on how we design the table.
dgrafxAuthor Commented:
Can you give an example using Mother's Day or Father's Day so I can see what you're referring to?
Here are some example calculations (from
--Mother's Day(second Sunday of May)
SELECT DateAdd(month,4,DateAdd(Year,DATEDIFF(Year,0, GETDATE()),0)) + 13
        -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0,  DateAdd(month,4,DateAdd(Year,DATEDIFF(Year,0, GETDATE()),0))), 0))
        +@@DateFirst+5)%7 as [Mothers Day]

--Father's Day (Third Sunday of June)
SELECT DateAdd(month,5,DateAdd(Year,DATEDIFF(Year,0, GETDATE()),0)) + 20
        -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0,  DateAdd(month,5,DateAdd(Year,DATEDIFF(Year,0, GETDATE()),0))), 0))
        +@@DateFirst+5)%7 as [Fathers Day]

-- Thanksgiving (Fourth Thursday in November)     
SELECT DateAdd(month,10,DateAdd(Year,DATEDIFF(Year,0, GETDATE()),0)) + 27
        -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0,  DateAdd(month,10,DateAdd(Year,DATEDIFF(Year,0, GETDATE()),0))), 0))
        +@@DateFirst+1)%7 as [Thanksgiving Day]

Open in new window

The idea is that you would update them as holiday's in Jim's table, the join to that table. You can generate Jim's table for the next N years and run with that.
My comment with functions, it's ok to use them. But if you are trying to check many holidays and the like, doing it in real time hurts performance. it might not be a very big issue for your scenario. But just keep in mind that a function is optimised differently from using (a set based method such as) a JOIN for example. If you have small datasets, it's fine. You won't notice. but if you have a large dataset, the function method might be a little slower.
dgrafxAuthor Commented:
Thanks a lot guys
I created my own smaller table and populated it with several years of holidays - using functions to calculate the dates for each year and inserted static holidays as well.
I now can union this along with the user created events.
I can see now that this was the way to go.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.