TSQL–Calculating Date of Irish/UK Mother’s Day
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 DATETIMESET @year = 2014SET @EpactCalc = (24 + 19 * (@year % 19)) % 30SET @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) / 44SET @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 -- SundayDECLARE @days_to_first_sunday INT, @days_to_fourth_sunday INTSET @days_to_first_sunday = 7 - datepart(dw, @start_of_lent)SET @days_to_fourth_sunday = 28 - @days_to_first_sundayselect DATEADD(DD, @days_to_fourth_sunday, @start_of_lent)
Comments