Wednesday, October 1, 2014

TSQL–Calculating Date of Irish/UK Mother’s Day

October 01, 2014 Posted by Jason Irwin No comments

While in the US Mother’s day falls on the second Sunday of May, in western Europe things are a little more complicated. Mother’s Day falls on Mothering Sunday which is the 4th Sunday in Lent. I couldn’t find a T-SQL script to calculate this date, so I rolled my own (with a little help from SO). Let me know if you can have a more succinct method of capturing the same. Here’s the script:

 /* CALCULATE YEAR 
 http://stackoverflow.com/questions/2192533/function-to-return-date-of-easter-for-the-given-year
 */
DECLARE  @year INT, 
@EpactCalc INT,  
@paschal_days_calc INT, 
@num_of_days_to_sunday INT, 
@easter_sunday_month INT, 
@easter_sunday_day INT,
@easter_sunday DATETIME,
@start_of_lent DATETIME
SET @year = 2014
SET @EpactCalc = (24 + 19 * (@year % 19)) % 30 
SET @paschal_days_calc = @EpactCalc - (@EpactCalc / 28) 
SET @num_of_days_to_sunday = @paschal_days_calc - ( 
	(@year + @year / 4 + @paschal_days_calc - 13) % 7 
) 
SET @easter_sunday_month = 3 + (@num_of_days_to_sunday + 40) / 44 
SET @easter_sunday_day = @num_of_days_to_sunday + 28 - ( 
	31 * (@easter_sunday_month / 4) 
) 
 
SELECT @easter_sunday = CONVERT      (  SMALLDATETIME, 
            RTRIM(@year)  
    + RIGHT('0'+RTRIM(@easter_sunday_month), 2)  
    + RIGHT('0'+RTRIM(@easter_sunday_day), 2)  
)
/* CALCULATE START OF LENT */
SET @start_of_lent = DATEADD(DD,-46,@easter_sunday)
/* CALCULATE MOTHERING SUNDAY */
	
set datefirst 7 -- Sunday
DECLARE @days_to_first_sunday INT, @days_to_fourth_sunday INT
SET @days_to_first_sunday = 7 - datepart(dw, @start_of_lent)
SET @days_to_fourth_sunday = 28 - @days_to_first_sunday
select DATEADD(DD,  @days_to_fourth_sunday, @start_of_lent)

0 comments: