Calculating Good Friday in T-SQL

Sep 19

A few years back I had to generate a table using T-SQL that lists Canadian holidays for the upcoming x number of years. This was part of our migration from our old Nortel phone system to Skype for Business Public Telephony. This requirement was for programming the after-hours and holiday menus for the upcoming number of years. I think back to this task every now and then because it was a fun little challenge.

Most of the holidays are simple to calculate because they fall on the same day every year but Easter or Good Friday are based on the Paschal Full Moon following the spring equinox, which changes year to year. So here's a script based on this post, which is based on a script from NOAA, to calculate Good Friday:

CREATE FUNCTION dbo.uf_GetGoodFriday (@Year INT)
RETURNS datetime 
WITH EXECUTE AS CALLER
AS 
BEGIN 
	DECLARE @intYear INT, @EpactCalc INT, @PaschalDaysCalc INT, @NumOfDaysToSunday INT, @EasterMonth INT, @EasterDay INT 

	SET @EpactCalc = (24 + 19 * (@Year % 19)) % 30 
	SET @PaschalDaysCalc = @EpactCalc - (@EpactCalc / 28) 
	SET @NumOfDaysToSunday = @PaschalDaysCalc - ((@Year + @Year / 4 + @PaschalDaysCalc - 13) % 7) 

	SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40) / 44 
	SET @EasterDay = @NumOfDaysToSunday + 28 - (31 * (@EasterMonth / 4)) 

	RETURN ( 
		SELECT DATEADD(dd,-2,CONVERT(DATETIME, CONVERT(VARCHAR(2),@EasterMonth) + '/' + CONVERT(VARCHAR(2),@EasterDay) + '/' + CONVERT(VARCHAR(4), @Year)))
	) 
END
GO

Trying it out for next year:

SELECT uf_GetGoodFriday (2021)

Returns:

2021-04-02 00:00:00.000