Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculate Easter Date Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, March 25, 2008 9:25 AM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, December 04, 2012 7:27 AM Points: 208, Visits: 49
 Comments posted to this topic are about the item Calculate Easter Date
Post #474167
 Posted Monday, June 02, 2008 7:26 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 9:44 AM Points: 4,863, Visits: 8,394
 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
Post #509833
 Posted Monday, June 02, 2008 7:35 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, December 04, 2013 8:37 AM Points: 1,044, Visits: 2,987
 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
Post #509842
 Posted Monday, June 02, 2008 7:56 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 9:44 AM Points: 4,863, Visits: 8,394
 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
Post #509863
 Posted Monday, June 02, 2008 8:09 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, December 04, 2013 8:37 AM Points: 1,044, Visits: 2,987
 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
Post #509879
 Posted Monday, June 02, 2008 8:19 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 9:44 AM Points: 4,863, Visits: 8,394
 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
Post #509886
 Posted Monday, June 02, 2008 8:37 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, December 04, 2013 8:37 AM Points: 1,044, Visits: 2,987
 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
Post #509900
 Posted Monday, June 02, 2008 5:22 PM
 Grasshopper Group: General Forum Members Last Login: Wednesday, December 04, 2013 6:12 PM Points: 18, Visits: 129
 I had a need to do this some time ago. I implemented a well known algorithm from an English standards organisation (I've forgotten exactly who).This is a little shorter than your's 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( convert(datetime, convert(varchar(4),@iYear) + '-' + convert(varchar(2), @iMonth) + '-' + convert(varchar(2), @iDay)))End
Post #510350
 Posted Wednesday, August 22, 2012 7:58 AM
 SSC Eights! Group: General Forum Members Last Login: Thursday, November 21, 2013 2:05 PM Points: 928, Visits: 675
 Not a single comment in the code: ok you can work it out, but I reckon that's a fail.
Post #1348413

 Permissions