Written by 
This is usually a pain in the butt trying to workout, some developers just use a static table or list manually generated. However, almost all UK bank holidays can be calculated dynamically, apart from the odd one that is just a one off like the coronation of new monarch or what not. So for those special cases you have no choice but to manually add them in somewhere/somehow as there is no algorithm anyone could write to calculate them (unless you know the future). Anyway, to help alleviate some of the pain of doing this I’m posting some code I’ve used and refined over the years so others might benefit (hopefully there’s no bugs in it). This is a set of functions for SQL Server and they’ve been tested with normal vanilla SQL Server, but you should be able to modify them for other systems. Before I forget, here is a link to the UK government web page which details all the UK bank holidays, might be useful for reference…
Below is a list of each of the dates we’ll need to calculate:-
- New years day
- Good Friday
- Easter Monday
- Early May bank holiday
- Spring bank holiday
- Summer bank holiday
- Christmas day
- Boxing day
I’ve tried to comment the code as clearly as possible, also I’ve tried to make some of the code overly clear. By that I mean some of it could be optimised further, but for clarity I’ve coded it the way I have on purpose. Feel free to copy, use, optimise, rename anything or change the schema I’ve used…
Here’s a function for new years day:-
CREATE FUNCTION [Chronological].[fnGetBankHolidayDateForNewYearsDay] (
@year INT
)
RETURNS DATE AS
BEGIN
-- Calculates the bank holiday for new years day on the specified year. See
-- the link below for UK Government details on bank holidays. Other countries
-- may have different calculations https://www.gov.uk/bank-holidays?mod=article_inline
-- First get the real new years day date for the specified year
DECLARE @newYearsDay DATE = DATEFROMPARTS(@year, 1, 1)
-- If it falls on a weekend we need to adjust the date for the bank holiday...
RETURN CASE
-- If its on Saturday, we need to skip 2 days to get the Monday for our bank holiday
WHEN DATENAME(WEEKDAY, @newYearsDay) = 'Saturday' THEN DATEADD(DAY, 2, @newYearsDay)
-- If its on Sunday, we just need to skip to the next day to get the Monday for our bank holiday
WHEN DATENAME(WEEKDAY, @newYearsDay) = 'Sunday' THEN DATEADD(DAY, 1, @newYearsDay)
-- Otherwise, its just a normal week day so no adjustment needed ;-)
ELSE @newYearsDay
END
END
Now for Good Friday. This, like Easter Monday is of course related to Easter Sunday, so we just calculate relative to Easter Sunday (which I’ll post a copy of later as that one is much more tricky!):-
CREATE FUNCTION [Chronological].[fnGetBankHolidayDateForGoodFriday](
@year INT
)
RETURNS DATE AS
BEGIN
-- Much easier than Easter Sunday, as its always just the Friday (2 days) before! ;-)
RETURN DATEADD(DAY, -2, [Chronological].[fnGetBankHolidayDateForEasterSunday](@year))
END
Next comes Easter Monday (again, similar to Good Friday, this is relative to Easter Sunday):-
CREATE FUNCTION [Chronological].[fnGetBankHolidayDateForEasterMonday](
@year INT
)
RETURNS DATE AS
BEGIN
-- Much easier than Easter Sunday, as its always just the next day! ;-)
RETURN DATEADD(DAY, 1, [Chronological].[fnGetBankHolidayDateForEasterSunday](@year))
END
The next one is the early May bank holiday:-
CREATE FUNCTION [Chronological].[fnGetBankHolidayDateForEarlyMay] (
@year INT
)
RETURNS DATE AS
BEGIN
-- Early May bank holiday is first Monday in May, so lets start on May the 1st
-- find the Monday that comes on or after this date. See this link below for UK
-- Government details on bank holidays
-- https://www.gov.uk/bank-holidays?mod=article_inline
-- Start with the first date in May for the specified year
DECLARE @earlyMayDay DATE = DATEFROMPARTS(@year, 5, 1)
-- Adjust if not Monday...
RETURN CASE
-- It is a Monday, so all done, no adjustment needed...
WHEN DATENAME(WEEKDAY, @earlyMayDay) = 'Monday' THEN @earlyMayDay
-- Its not a Monday, so we adjust the date and move to the following Monday
WHEN DATENAME(WEEKDAY, @earlyMayDay) = 'Tuesday' THEN DATEADD(DAY, 6, @earlyMayDay)
WHEN DATENAME(WEEKDAY, @earlyMayDay) = 'Wednesday' THEN DATEADD(DAY, 5, @earlyMayDay)
WHEN DATENAME(WEEKDAY, @earlyMayDay) = 'Thursday' THEN DATEADD(DAY, 4, @earlyMayDay)
WHEN DATENAME(WEEKDAY, @earlyMayDay) = 'Friday' THEN DATEADD(DAY, 3, @earlyMayDay)
WHEN DATENAME(WEEKDAY, @earlyMayDay) = 'Saturday' THEN DATEADD(DAY, 2, @earlyMayDay)
-- Otherwise its Sunday, so just add 1 day to get first Monday in the month ;-)
ELSE DATEADD(WEEKDAY, 1, @earlyMayDay)
END
END
Next is spring bank holiday:-
CREATE FUNCTION [Chronological].[fnGetBankHolidayDateForSpring] (
@year INT
)
RETURNS DATE AS
BEGIN
-- Spring bank holiday is the last Monday in May in the UK. Other countries
-- may have different calculations. See the link here for UK Government listing
-- on bank holidays https://www.gov.uk/bank-holidays?mod=article_inline
-- Lets start with the last day in May for the specified year
DECLARE @springBankHoliday DATE = DATEADD(DAY, -1, DATEFROMPARTS(@year, 6, 1))
-- Do we need to adjust the date?
RETURN CASE
-- It is a Monday, so no adjustment needed...
WHEN DATENAME(WEEKDAY, @springBankHoliday) = 'Monday' THEN @springBankHoliday
-- It's not a Monday, so we need to keep going back to find a Monday
WHEN DATENAME(WEEKDAY, @springBankHoliday) = 'Tuesday' THEN DATEADD(DAY, -1, @springBankHoliday)
WHEN DATENAME(WEEKDAY, @springBankHoliday) = 'Wednesday' THEN DATEADD(DAY, -2, @springBankHoliday)
WHEN DATENAME(WEEKDAY, @springBankHoliday) = 'Thursday' THEN DATEADD(DAY, -3, @springBankHoliday)
WHEN DATENAME(WEEKDAY, @springBankHoliday) = 'Friday' THEN DATEADD(DAY, -4, @springBankHoliday)
WHEN DATENAME(WEEKDAY, @springBankHoliday) = 'Saturday' THEN DATEADD(DAY, -5, @springBankHoliday)
-- Its a Sunday so go back almost a week ;-)
ELSE DATEADD(WEEKDAY, -6, @springBankHoliday)
END
END
Next is the summer bank holiday:-
CREATE FUNCTION [Chronological].[fnGetBankHolidayDateForSummer] (
@year INT
)
RETURNS DATE AS
BEGIN
-- Summer bank holiday is the last Monday in August. Other countries apart from the UK
-- may have different calculations https://www.gov.uk/bank-holidays?mod=article_inline
-- First get the last date in August for the specified year
DECLARE @summerBankHoliday DATE = DATEADD(DAY, -1, DATEFROMPARTS(@year, 9, 1))
-- Now check if its Monday or not
RETURN CASE
-- Its a Monday, so no adjustment required
WHEN DATENAME(WEEKDAY, @summerBankHoliday) = 'Monday' THEN @summerBankHoliday
-- Go back to earlier to a Monday
WHEN DATENAME(WEEKDAY, @summerBankHoliday) = 'Tuesday' THEN DATEADD(DAY, -1, @summerBankHoliday)
WHEN DATENAME(WEEKDAY, @summerBankHoliday) = 'Wednesday' THEN DATEADD(DAY, -2, @summerBankHoliday)
WHEN DATENAME(WEEKDAY, @summerBankHoliday) = 'Thursday' THEN DATEADD(DAY, -3, @summerBankHoliday)
WHEN DATENAME(WEEKDAY, @summerBankHoliday) = 'Friday' THEN DATEADD(DAY, -4, @summerBankHoliday)
WHEN DATENAME(WEEKDAY, @summerBankHoliday) = 'Saturday' THEN DATEADD(DAY, -5, @summerBankHoliday)
-- Its a Sunday, so go back almost a week to the previous Monday
ELSE DATEADD(WEEKDAY, -6, @summerBankHoliday)
END
END
Now for the last bank holiday, Boxing day…
CREATE FUNCTION [Chronological].[fnGetBankHolidayDateForBoxingDay] (
@year INT
)
RETURNS DATE AS
BEGIN
-- This code is valid for use in the UK, other countries may need adjustments!
-- Christmas and Boxing day are of course 25th/26th December. For more info on
-- UK bank holidays see this link below
-- https://www.gov.uk/bank-holidays?mod=article_inline
-- First get the real Boxing day for the year
DECLARE @boxingDay DATE = DATEFROMPARTS(@year, 12, 26)
-- We need to adjust if this date is on the weekend...
RETURN CASE
-- If Boxing day is on a Saturday (and Boxing day the Sunday), then we jump 2 days
-- to get the Monday bank holiday. If Boxing day is on a Sunday though, this means
-- that Christmas day was on the Saturday, which means we allocate the Monday for
-- the Christmas bank holiday and the Tuesday for the Boxing day bank holiday. So
-- either way we still just need to skip 2 days to find our bank holiday
WHEN DATENAME(WEEKDAY, @boxingDay) IN ('Saturday', 'Sunday') THEN DATEADD(DAY, 2, @boxingDay)
-- Otherwise no adjustment needed ;-)
ELSE @boxingDay
END
END
Last but not least, we need one more function… even though this isn’t a bank holiday, its how we calculate the Easter bank holidays as they’re just relative dates to the Easter Sunday date. Before we go any further though, note this code is not mine… Since this algorithm is a right pain, and the fact that there are many different algorithms out there for this calculation, I’ve just copied an existing bit of code that was out there rather than inventing the wheel.
I’ve listed the link I copied the code from as credit to the original author (thanks dude). So, check out the link in the code below if you want to go down the Easter calculation rabbit hole 😉
CREATE FUNCTION [Chronological].[fnGetBankHolidayDateForEasterSunday](
@year INT
)
RETURNS DATE AS
BEGIN
-- See https://stackoverflow.com/questions/2192533/function-to-return-date-of-easter-for-the-given-year
-- for the original, cleaned up some code. Also see this page here https://en.wikipedia.org/wiki/Easter#Date
-- under the 'computation' section. Note that this function is only valid for years 1900 - 2199!
DECLARE
@epactCalculation INT
,@paschalDaysCalculation INT
,@numberOfDaysToSunday INT
,@easterMonth INT
,@easterDay INT
SET @epactCalculation = (24 + 19 * (@year % 19)) % 30
SET @paschalDaysCalculation = @epactCalculation - (@epactCalculation / 28)
SET @numberOfDaysToSunday = @paschalDaysCalculation - (
(@year + @year / 4 + @paschalDaysCalculation - 13) % 7
)
SET @easterMonth = 3 + (@numberOfDaysToSunday + 40) / 44
SET @easterDay = @numberOfDaysToSunday + 28 - (
31 * (@easterMonth / 4)
)
RETURN DATEFROMPARTS(@year, @easterMonth, @easterDay)
END
That’s all folks, hope someone finds this useful. I’ve put the code on GitHub for anyone who wants this packaged in a Visual Studio project. See here, obviously feel free to copy or submit a pull request if you find a bug. I’m planning to expand the project on Github with more useful data warehousing focused SQL functions, so keep an eye on it…
This is great, thank you!
I found a way to do the Christmas, Boxing Day and New Years day logic for a given year without a case condition. The case statement is more intuitive, but this could be more efficient.
DECLARE @year int = 2025
— Set the first day of the week so that Saturday = 1 and Sunday = 2
SET DATEFIRST 6;
— Use DATEADD to add the required number of days to the original date (01 January)
— Monday to Friday should add 0, Saturday should add 2 and Sunday should add 1
SELECT DATEADD(DAY
— Get the inverse of the offset weekday number by multiplying by -1, then add 3
— For Saturday and Sunday the result will be a positive integer, other days will be negative
,(((DATEPART(dw, DATEFROMPARTS(@year, 1, 1)))*-1)+3
— To convert all the negative integers to 0 but keep the positive ones the same
— add the absolute value and then divide by 2
+ ABS(((DATEPART(dw, DATEFROMPARTS(@year, 1, 1)))*-1)+3))
/2
— Add this value to the original date
,DATEFROMPARTS(@year, 1, 1)
) AS NewYearsDayHoliday