## Calculate Easter Date

 Author Message darren.sunderland SSC Veteran Group: General Forum Members Points: 233 Visits: 64 Comments posted to this topic are about the item Calculate Easter Date John Mitchell-245523 SSC-Dedicated Group: General Forum Members Points: 35233 Visits: 16681 Didn't work for me, I'm afraid. Tried to calculate this year's Easter date.Msg 242, Level 16, State 3, Procedure fcn_FindEasterSunday, Line 39The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.John majorbloodnock SSCrazy Group: General Forum Members Points: 2509 Visits: 3064 Interesting, John; it worked fine for me.Actually, I had to create a bit of vb-based code a while ago for calculating Easter correctly, and then ported it into a SQL function too. The function DASUN has provided gave the same results as mine for all years between 1800 and 9999, so if you want an alternative, try my version as below:CREATE FUNCTION [dbo].[fnEasterSunday] ( @YearVal int)RETURNS datetimeASBEGIN DECLARE @EasterDate datetime declare @a int, @b int, @c int, @d int, @e int, @f int, @g int, @h int, @j int, @m int, @k int, @mth int, @dy int, @easter datetime set @a = @yearval - (floor(@yearval/19) * 19) set @b = floor(@yearval/100) set @c = @yearval - (@b * 100) set @d = floor(@b/4) set @e = @b - (@d * 4) set @f = floor(@c/4) set @g = @c - (@f*4) set @h = floor(((8 * @b) + 13)/25) set @j = ((19 * @a) + (@b - @d - @h) + 15) - (floor(((19 * @a) + (@b - @d - @h) + 15)/30) * 30) set @m = floor((@a + 11 * @j)/319) set @k = ((2 * @e) + (2 * @f) - @g - @j + @m + 32) - (floor(((2 * @e) + (2 * @f) - @g - @j + @m + 32)/7) * 7) set @mth = floor((@j - @m + @k + 90)/25) set @dy = (@j - @m + @k + 19 + @mth) - (floor((@j - @m + @k + 19 + @mth) /32) * 32) set @easter = convert(datetime, str(@yearval) + '-' + str(@mth) + '-' + str(@dy), 120) if datepart(dw, @easter) = 1 begin select @easterdate = @easter end else begin select @easterdate = dateadd(d, 8 - datepart(dw, @easter), @easter) end RETURN @EasterDateENDAnd you might want to have a look at this website for the full details of the calculation.Hope this helps Semper in excretia, sumus solum profundum variat John Mitchell-245523 SSC-Dedicated Group: General Forum Members Points: 35233 Visits: 16681 Very interesting. But I think I'm happy enough just looking it up in a diary!Actually, I think it failed for me because my default language is British English. When I changed it to English, it worked. There's a challenge for Darren - make it compatible with all date formats!John majorbloodnock SSCrazy Group: General Forum Members Points: 2509 Visits: 3064 John Mitchell (6/2/2008)Very interesting. But I think I'm happy enough just looking it up in a diary!Actually, I think it failed for me because my default language is British English. When I changed it to English, it worked. There's a challenge for Darren - make it compatible with all date formats!JohnVery true, John. The change to Darren's script would simply be to add a third parameter into the "SET @dtEasterSunday = CONVERT(" line, so that it specified the 120 date format (i.e. year-month-day), and it should work fine.I must admit that I initially found my investigations into the various bank holiday dates really tedious, but there were one or two little gems that were interesting in a somewhat anally retentive way. For instance, I found out that, in the UK, when Christmas falls on a Saturday, the following Monday is the Boxing Day bank holiday and the bank holiday in lieu of Christmas Day occurs afterwards on the Tuesday . Semper in excretia, sumus solum profundum variat John Mitchell-245523 SSC-Dedicated Group: General Forum Members Points: 35233 Visits: 16681 majorbloodnock (6/2/2008)For instance, I found out that, in the UK, when Christmas falls on a Saturday, the following Monday is the Boxing Day bank holiday and the bank holiday in lieu of Christmas Day occurs afterwards on the Tuesday .I suppose that depends which way you look at it - you could say that Monday is in lieu of Christmas Day and Tuesday is in lieu of Boxing Day... or is there actually a rule somewhere that defines it in the way you say? You can't always apply formulas to bank holiday calculations - for example in 1995 the government changed the May bank holiday from the 1st to the 8th to celebrate the 50th anniversary of VE Day, and seven years after that they gave us an extra day at Whitsun to celebrate the Queen's golden jubilee.John majorbloodnock SSCrazy Group: General Forum Members Points: 2509 Visits: 3064 John Mitchell (6/2/2008)majorbloodnock (6/2/2008)For instance, I found out that, in the UK, when Christmas falls on a Saturday, the following Monday is the Boxing Day bank holiday and the bank holiday in lieu of Christmas Day occurs afterwards on the Tuesday .I suppose that depends which way you look at it - you could say that Monday is in lieu of Christmas Day and Tuesday is in lieu of Boxing Day... or is there actually a rule somewhere that defines it in the way you say?There is indeed. Boxing Day apparently derives from an old workplace tradition where a "collection" box was opened and the year's accumulated contents distributed amongst the employees, so strictly, Boxing Day is recognised officially as the first working day after Christmas Day (i.e. 25th December). Since our Government provides a bank holiday in lieu of Christmas when that day falls on a weekend, the bank holiday provided then rolls past the Boxing Day holiday and onto the next available working day. Hence, if the 25th falls on a Saturday, Boxing Day is celebrated on the Sunday, Boxing Day actually occurs on 27th (and people have a holiday as a result) and then the Christmas Day Bank Holiday comes last on 28th. Phew. Semper in excretia, sumus solum profundum variat John Chapman SSC-Enthusiastic Group: General Forum Members Points: 135 Visits: 172 I had a need to do this some time ago. I implemented a well known algorithm from an English standards organization (I've forgotten exactly who).This is a little shorter than yours and has a pumpkin date associated with it (but it's a long way out).Here it is:CREATE function fn_Calc_Easter_Sunday(@iYear int)returns DateTimeasbeginDeclare @iD int, @iE int, @iQ int, @iMonth int, @iDay intSelect @iD = 255 - 11 * (@iYear % 19)If @iD > 50 Select @iD = (@iD-21) % 30 + 21If @iD > 48 Select @iD = @iD - 1Select @iE = (@iYear + @iYear/4 + @iD + 1) % 7Select @iQ = @iD + 7 - @iE;If @iQ < 32 Begin Select @iMonth = 3 Select @iDay = @iQ EndElse Begin Select @iMonth = 4 Select @iDay = @iQ - 31 EndRETURN ( SELECT CONVERT(DATETIME, [Year] + REPLICATE('0', 2-LEN([Month])) + [Month] + REPLICATE('0', 2-LEN([Day])) + [Day]) FROM (SELECT CAST(@iYear AS VARCHAR(4)) as [Year], CAST(@iMonth AS VARCHAR(2)) AS [Month], CAST(@iDay AS VARCHAR(2)) AS [Day]) a );End david.wright-948385 SSCommitted Group: General Forum Members Points: 1700 Visits: 991 Not a single comment in the code: ok you can work it out, but I reckon that's a fail. Iwas Bornready SSC-Insane Group: General Forum Members Points: 22602 Visits: 885 Thanks for the script.