May 5, 2008 at 3:35 pm
Comments posted to this topic are about the item Calculate Holidays
Hawkeye67
November 24, 2016 at 3:13 am
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
November 24, 2016 at 4:57 am
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!
November 24, 2016 at 8:36 am
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
November 28, 2016 at 8:43 am
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.
December 15, 2016 at 6:09 am
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.
PaladinTech
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
December 28, 2023 at 10:28 pm
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')))))) * 7
PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')))))) * 7
PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')))))) * 7
PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')))))) * 7
PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')))))) * 7
PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')))))) * 7
PRINT '--- 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 ---'
December 28, 2023 at 10:29 pm
This was removed by the editor as SPAM
December 29, 2023 at 6:11 pm
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
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply