Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculate Easter Date Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2008 9:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:08 AM
Points: 211, Visits: 60
Comments posted to this topic are about the item Calculate Easter Date
Post #474167
Posted Monday, June 2, 2008 7:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 5,437, Visits: 10,133
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
Posted Monday, June 2, 2008 7:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 6:00 AM
Points: 1,049, Visits: 3,012
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
Posted Monday, June 2, 2008 7:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 5,437, Visits: 10,133
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 2, 2008 8:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 6:00 AM
Points: 1,049, Visits: 3,012
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!

John


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 .


Semper in excretia, sumus solum profundum variat
Post #509879
Posted Monday, June 2, 2008 8:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 5,437, Visits: 10,133
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 2, 2008 8:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 6:00 AM
Points: 1,049, Visits: 3,012
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 2, 2008 5:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:07 PM
Points: 21, Visits: 147
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
Posted Wednesday, August 22, 2012 7:58 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 988, Visits: 813
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 »

Add to briefcase

Permissions Expand / Collapse