# Calculate Holidays

• Comments posted to this topic are about the item Calculate Holidays

Hawkeye67

• select * from dbo.GetHolidays(16)

ItemID Holiday_Title Holiday_Date

1 Easter Sunday 2016-04-10

2 Thanksgiving 2016-11-24

3 Memorial Day 2016-05-30

4 Labor Day 2016-09-05

select * from dbo.GetHolidays(2016)

ItemIDHoliday_TitleHoliday_Date

1 Easter Sunday2016-03-27

2 Thanksgiving2016-11-24

3 Memorial Day2016-05-30

4 Labor Day 2016-09-05

• This code is very difficult to understand - there are no supporting comments and a lot of variables named as single letters.

Some documentation of how it works would be useful!

• An alternative way of doing it

but better was to create a calendar table with relevant columns, like the ones in the CTE's in the function, then its just a select to that table

`CREATE FUNCTION dbo.GetHolidays_2(`

`@Year INT = 0`

`)`

`RETURNS @Holidays TABLE(`

`ItemID INT IDENTITY(1,1)`

`, Holiday_Title VARCHAR(50)`

`, Holiday_Date DATETIME`

`)`

`AS`

`BEGIN`

`DECLARE @Month INT`

`, @Day INT`

`, @Easter DATE`

`-- Calculate Easter Sunday`

`-- Alogrithm modeled after VB2TheMax code`

`DECLARE @g INT`

`, @C INT`

`, @h INT`

`, @i INT`

`, @j-2 INT`

`, @l INT`

`SET @g = @Year % 19`

`SET @C = @Year / 100`

`SET @h = ((@c - (@c / 4) - ((8 * @C + 13) / 25) + (19 * @g) + 15) % 30)`

`SET @i = @h - ((@h / 28) * (1 - (@h /28) * (29 / (@h + 1)) * ((21 - @g) / 11)))`

`SET @j-2 = ((@Year + (@Year / 4) + @i + 2 - @C + (@c / 4)) % 7)`

`SET @l = @i - @j-2`

`SET @Month = 3 + ((@l + 40) / 44)`

`SET @Day = @l + 28 - (31 * (@Month / 4))`

`SET @Easter = DATEADD(DAY,@Day- 1,DATEADD(MONTH,@Month-1,DATEADD(YEAR, (@Year - DATEPART(YEAR, 0)), 0)))`

`DECLARE @FirstDayOfGivenYear DATE`

`, @LastDayOfGivenYear DATE`

`SELECT @FirstDayOfGivenYear = DATEADD(DAY,0,DATEADD(MONTH,0,DATEADD(YEAR, (@Year - DATEPART(YEAR, 0)), 0)))`

`, @LastDayOfGivenYear = DATEADD(DAY,-1,DATEADD(MONTH,0,DATEADD(YEAR, (@Year - DATEPART(YEAR, 0) + 1), 0)))`

`;WITH DATES AS (`

`SELECT@FirstDayOfGivenYearAS date_`

`, DATEPART(DW, @FirstDayOfGivenYear)AS dw`

`, DATEPART(MONTH, @FirstDayOfGivenYear)AS month_`

`, DATEPART(WEEK, @FirstDayOfGivenYear)AS week_`

`, 1AS ocurrency_in_month`

`UNIONALL`

`SELECTDATEADD(DAY, 1, date_)AS date_`

`, DATEPART(DW, DATEADD(DAY, 1, date_))AS dw`

`, DATEPART(MONTH, DATEADD(DAY, 1, date_))AS month_`

`, DATEPART(WEEK, DATEADD(DAY, 1, date_))AS week_`

`, CASE`

`WHEN DATEPART(DAY, DATEADD(DAY, 1, date_)) = 1`

`THEN 1`

`ELSE`

`CASE WHEN DATEPART(DAY, DATEADD(DAY, 1, date_)) IN (8, 15, 22, 29)`

`THEN ocurrency_in_month + 1`

`ELSE ocurrency_in_month`

`END`

`ENDAS ocurrency_in_month`

`FROMDATES`

`WHEREdate_ < @LastDayOfGivenYear`

`)`

`, DATES_2AS (`

`SELECTD.date_`

`, D.dw`

`, D.month_`

`, D.week_`

`, D.ocurrency_in_month`

`, M.max_ocurrency_in_month`

`FROMDATESD`

`INNERJOIN`

`(`

`SELECTmonth_`

`, dw`

`, MAX(ocurrency_in_month)AS max_ocurrency_in_month`

`FROMDATES`

`GROUPBY month_, dw`

`)M`

`ONM.month_ = D.month_`

`andM.dw = D.dw`

`)`

`, HOLLIDAYS AS (`

`SELECT'Easter Sunday'AS Holiday_Title`

`, @EasterAS Holiday_Date`

`UNION ALL`

`SELECTCASE `

`WHEN month_ = 11AND dw = 5AND ocurrency_in_month = 4`

`THEN 'Thanksgiving'-- 4º Thursday of November`

`WHEN month_ = 9AND dw = 2AND ocurrency_in_month = 1`

`THEN 'Labor Day'-- 1º Monday of September`

`WHEN month_ = 5AND dw = 2AND ocurrency_in_month = max_ocurrency_in_month`

`THEN 'Memorial Day'-- Last Monday of May`

`ENDAS Holiday_Title`

`, date_AS Holiday_Date`

`FROMDATES_2`

`)`

`INSERTINTO @Holidays (Holiday_Title, Holiday_Date)`

`SELECTHoliday_Title, Holiday_Date`

`FROMHOLLIDAYS`

`WHEREHoliday_Title IS NOT NULL`

`OPTION (MAXRECURSION 0)`

`RETURN`

`END`

• george.p (11/24/2016)

This code is very difficult to understand - there are no supporting comments and a lot of variables named as single letters.

Some documentation of how it works would be useful!

I agree. Still kind of nice though.

• I just became aware of the fact that SQL Server Central subscribers were still accessing this version of Calculate Holidays. This was written years ago and I have since done many different versions of this, including preparing an actual table of holidays, for my clients. I was also aware of the fact of the difference between using the two digit year and the four digit year. I will try and find my most recent version for SQL Server Central posting.

Sorry for the late response. I am semi-retired and spending a lot of time hunting the "king" of game birds, the Ruffed Grouse, with my new hunting dog.

Take care.

I don't understand why I am called a "rookie" when I have been programming SQL Server since the introduction of version 6.5? :unsure:

Hawkeye67

• I recently needed to calculate Thanksgiving. One of the more valuable resources that I found calculated several US holidays but had faulty logic when it came to Thanksgiving. Instead of trying to fix the logic, I started from scratch and added comments.

I'll probably take the original script and convert it to a stored proc which then inserts the holidays into the table that we use.

`DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, @Date)), @Date)))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, @Date)), @Date)))))`
`PRINT '--- Days to end of week ---'PRINT 7-DatePart(dw, '11/1/2014') PRINT 7-DatePart(dw, '11/1/2015') PRINT 7-DatePart(dw, '11/1/2016') PRINT 7-DatePart(dw, '11/1/2017') PRINT 7-DatePart(dw, '11/1/2018') PRINT 7-DatePart(dw, '11/1/2019') PRINT '--- Calculate weekending date that the 1st falls in ---'PRINT DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')PRINT DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')PRINT DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')PRINT DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')PRINT DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')PRINT DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')PRINT '--- Calculate the Thursday before the 1st ---'PRINT '--- Yes, we jump forward to Saturday and then jump back to Thursday. It seemed easier given that the math will always give us a positive number. If we use 5 (Thursday), the math could return a positive or negative number that would have to be adjusted. ---'PRINT DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')))PRINT DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')))PRINT DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')))PRINT DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')))PRINT DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')))PRINT DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')))PRINT '--- Add 21 Days to get the Thursday that occurs in 4 weeks ---'PRINT '--- This will either be Thanksgiving or the Thursday prior dependeing on the day of the week that the 1st falls under. We adjust for this in the next step. ---'PRINT DateAdd(d, 21, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')))))PRINT DateAdd(d, 21, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')))))PRINT DateAdd(d, 21, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')))))PRINT DateAdd(d, 21, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')))))PRINT DateAdd(d, 21, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')))))PRINT DateAdd(d, 21, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')))))PRINT '--- Add in another 7 days for each month where the Thursday Prior is in a different month ---'PRINT '--- Get the month of the Thursday prior to the first ---'PRINT '--- This will be used to determine if another 7 days needs to be added ---'PRINT Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')))))PRINT Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')))))PRINT Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')))))PRINT Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')))))PRINT Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')))))PRINT Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')))))PRINT '--- Calculate the additional 7 days to add in by getting the difference between the month that the prior Thursday falls in (October) and November---'PRINT '--- If the prior Thursday is in November, the math will give us 0 which when multiplied by 7 will gives us 0 days to add in---'PRINT '--- If the prior Thursday is in October, the math will give us 1 which will give us 7 days to add in ---'PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')))))) * 7PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')))))) * 7PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')))))) * 7PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')))))) * 7PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')))))) * 7PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')))))) * 7PRINT '--- Bring it all home and do not even try to understand this ---'PRINT DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')))))PRINT DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')))))PRINT DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')))))PRINT DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')))))PRINT DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')))))PRINT DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')))))PRINT '--- You have just eaten an elephant ---'`
• This was removed by the editor as SPAM

• Here is something - just for a different perspective:

`  CREATE Function [dbo].[fnGetUSHolidays] (        @inputYear int = 1792        )Returns Table With schemabinding             As  Return /* ===========================================================================================   Author:      Jeff Williams   Created:     10/17/2019   Description: Returns a list of defined holidays for the input year        To determine the holidays, we use a list of holiday definitions:            Observed        0 = not observed                            1 = is observed (sets IsHoliday Flag)                            2 = observed on fixed date only (no offset - sets IsHoliday Flag)            MonthNumber     the month where the holiday occurs            DayNumber       the day of the holiday (fixed holidays)            WeekDayNumber   the weekday of the holiday (0 = Monday, 6 = Sunday)            Occurrence      the occurrence in the month, -1 is last occurrence in month            AddDays         day offset to be added to the calculation            HolidayName     the name of the holiday        For Holidays that are defined on a specific day (New Year's, Christmas), check         if that day falls on a weekend.  If the day falls on a weekend, offset the day        to the nearest week day and mark that day as a holiday in addition to the actual        holiday.          For fixed holidays that are only observed on the specific day (ex: Halloween), use        2 for Observed and the weekend offset will not be calculated.  Weekend offsets will        not be calculated for any non-observed holidays.                           Ex. If New Year's Day falls on a Saturday for the given year, define 12/31            of the previous year as a holiday - append (observed) to the name and            add this date to the list of holidays for that year.          For Holidays that are calculated as the next day, for example        'Day after Thanksgiving' - calculate as the Holiday and use AddDays to adjust for         the correct date.   Called From:        Calendar Dim Function and direct queries           Example Calls:        Select * From dbo.fnGetUSHolidays(0001);        Select * From dbo.fnGetUSHolidays(2020);           Revision History     Date       Edited By       Change     ---------- --------------- --------------------------------------------------------------     10/17/2019 Jeff Williams   Created   =========================================================================================== */   With holidays     As ( Select h.Observed      , MonthNumber = cast(h.MonthNumber As int)      , DayNumber = cast(h.DayNumber As int)      , Occurrence = cast(h.Occurrence As int)      , AddDays = cast(h.AddDays As int)      , HolidayName = cast(h.HolidayName As varchar(25))      , FirstOfMonth = datefromparts(@inputYear, h.MonthNumber, 7)      , WeekDayDate = dateadd(day, h.WeekDayNumber, cast('0001-01-01' As date))   From ( Values (1, 01,   01, Null, Null, Null, 'New Year''s Day')              -- WeekDayNumber:  0 = Monday      , (1, 01, Null,    0,    3,    0, 'Martin Luther King Day')       --                 1 = Tuesday      , (1, 02, Null,    0,    3,    0, 'President''s Day')             --                 2 = Wednesday      , (2, 03,   17, Null, Null, Null, 'St Patrick''s Day')            --                 3 = Thursday      , (0, 05, Null,    6,    2,    0, 'Mother''s Day')                --                 4 = Friday          , (1, 05, Null,    0,   -1,    0, 'Memorial Day')                 --                 5 = Saturday         , (0, 06, Null,    6,    3,    0, 'Father''s Day')                --                 6 = Sunday                     , (1, 07,   04, Null, Null, Null, 'Independence Day')                  , (1, 09, Null,    0,    1,    0, 'Labor Day')                    -- Occurrence:    -1 = Last day in the month      , (1, 10, Null,    0,    2,    0, 'Columbus Day')                 --                 n = # day in the month      , (0, 10,   31, Null, Null, Null, 'Halloween')                    --                     (2nd Monday, 4th Thursday)      , (1, 11,   11, Null, Null, Null, 'Veterans Day')                      , (1, 11, Null,    3,    4,    0, 'Thanksgiving Day')             -- AddDays:        n = Add # days to calculation      , (0, 11, Null,    3,    4,    1, 'Day after Thanksgiving')            , (1, 12,   25, Null, Null, Null, 'Christmas Day')                     ) As h(Observed, MonthNumber, DayNumber, WeekDayNumber, Occurrence, AddDays, HolidayName)        ) Select IsHoliday = cast(h.Observed As bit)      , obs.HolidayName      , obs.HolidayDate   From holidays As h  Cross Apply (Values (eomonth(datefromparts(@inputYear, h.MonthNumber, 1))                    , (datefromparts(@inputYear, h.MonthNumber, h.DayNumber))))                                         As dd(eom, FixedHoliday)  Cross Apply (Values (dateadd(day, datediff(day, h.WeekDayDate, h.FirstOfMonth) / 7 * 7, h.WeekDayDate)))              As mm(FirstDayInMonth)  Cross Apply (Values (coalesce(nullif(h.Occurrence, -1), datediff(day, mm.FirstDayInMonth, dd.eom) / 7 + 1) - 1))      As dm(Occurrence)  Cross Apply (       Select HolidayDate = coalesce(ob.ObservedDate, dateadd(day, dm.Occurrence * 7 + h.AddDays, mm.FirstDayInMonth))            , HolidayName = concat(h.HolidayName, iif(dd.FixedHoliday <> ob.ObservedDate, ' (observed)', ''))         From (Values (datediff(day, cast('0001-01-01' As date), dd.FixedHoliday) % 7))                                As dt(dow)        Cross Apply (Values (dateadd(day, -1, dd.FixedHoliday), (dt.dow / 5) & ~(dt.dow / 6) & h.Observed)                          , (dd.FixedHoliday, 1), (dateadd(day, 1, dd.FixedHoliday), (dt.dow / 6) & h.Observed))       As ob(ObservedDate, IsObserved)        Where ob.IsObserved = 1              ) As obs;GO`

This can be used directly - or used to populate a Calendar table.  To work with US bank holidays - remove the first row from ob (Observed) to remove the previous Friday when the holiday falls on a Saturday.

You can also review this: https://www.sqlservercentral.com/scripts/calculating-easter-in-sql for several ways to calculate Easter.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

Viewing 9 posts - 1 through 8 (of 8 total)