Calculate Easter Date

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by DASUN » Calculate Easter Date

Calculate Easter Date

Rate Topic

Display Mode

Topic Options

Author

Message

darren.sunderland

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

John Mitchell-245523

Posted Monday, June 02, 2008 7:26 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 7:03 AM
Points: 4,443, Visits: 7,249

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 39
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

John

Post #509833

majorbloodnock

Posted Monday, June 02, 2008 7:35 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 10:52 AM
Points: 1,043, Visits: 2,945

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 datetime
AS
BEGIN
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 @EasterDate

END

And 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

John Mitchell-245523

Posted Monday, June 02, 2008 7:56 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 7:03 AM
Points: 4,443, Visits: 7,249

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

majorbloodnock

Posted Monday, June 02, 2008 8:09 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 10:52 AM
Points: 1,043, Visits: 2,945

John Mitchell (6/2/2008)

Very 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 Hehe

.

Semper in excretia, sumus solum profundum variat

Post #509879

John Mitchell-245523

Posted Monday, June 02, 2008 8:19 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 7:03 AM
Points: 4,443, Visits: 7,249

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 Hehe

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

majorbloodnock

Posted Monday, June 02, 2008 8:37 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 10:52 AM
Points: 1,043, Visits: 2,945

John Mitchell (6/2/2008)

majorbloodnock (6/2/2008)

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

John Chapman

Posted Monday, June 02, 2008 5:22 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:43 PM
Points: 15, Visits: 112

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 DateTime
as
begin

Declare @iD int,
@iE int,
@iQ int,
@iMonth int,
@iDay int

Select @iD = 255 - 11 * (@iYear % 19)

If @iD > 50
Select @iD = (@iD-21) % 30 + 21

If @iD > 48
Select @iD = @iD - 1

Select @iE = (@iYear + @iYear/4 + @iD + 1) % 7
Select @iQ = @iD + 7 - @iE;

If @iQ < 32
Begin
Select @iMonth = 3
Select @iDay = @iQ
End

Else
Begin
Select @iMonth = 4
Select @iDay = @iQ - 31
End

return( convert(datetime, convert(varchar(4),@iYear) + '-' + convert(varchar(2), @iMonth) + '-' + convert(varchar(2), @iDay)))
End

Post #510350

david.wright-948385

Posted Wednesday, August 22, 2012 7:58 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 4:41 AM
Points: 909, Visits: 596

Not a single comment in the code: ok you can work it out, but I reckon that's a fail.

Post #1348413

« Prev Topic | Next Topic »

Permissions