March 25, 2008 at 9:25 am
Comments posted to this topic are about the item Calculate Easter Date
June 2, 2008 at 7:26 am
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
June 2, 2008 at 7:35 am
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-2 int,
@C int,
@d int,
@e int,
@f int,
@g int,
@h int,
@j-2 int,
@m int,
@k int,
@mth int,
@dy int,
@easter datetime
set @a = @yearval - (floor(@yearval/19) * 19)
set @b-2 = floor(@yearval/100)
set @C = @yearval - (@b * 100)
set @d = floor(@b/4)
set @e = @b-2 - (@d * 4)
set @f = floor(@c/4)
set @g = @C - (@f*4)
set @h = floor(((8 * @b-2) + 13)/25)
set @j-2 = ((19 * @a) + (@b - @d - @h) + 15) - (floor(((19 * @a) + (@b - @d - @h) + 15)/30) * 30)
set @m = floor((@a + 11 * @j-2)/319)
set @k = ((2 * @e) + (2 * @f) - @g - @j-2 + @m + 32) - (floor(((2 * @e) + (2 * @f) - @g - @j-2 + @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, suus solum profundum variat
June 2, 2008 at 7:56 am
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
June 2, 2008 at 8:09 am
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 :hehe:.
Semper in excretia, suus solum profundum variat
June 2, 2008 at 8:19 am
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
June 2, 2008 at 8:37 am
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 :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?
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, suus solum profundum variat
June 2, 2008 at 5:22 pm
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 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 (
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
August 22, 2012 at 7:58 am
Not a single comment in the code: ok you can work it out, but I reckon that's a fail.
May 10, 2016 at 6:59 am
Thanks for the script.
May 10, 2016 at 7:00 am
david.wright-948385 (8/22/2012)
Not a single comment in the code: ok you can work it out, but I reckon that's a fail.
I hate it when there are no comments.
April 7, 2017 at 9:37 pm
we can calculate easter date without sql data like this When Is Easter Sunday
April 8, 2017 at 10:17 am
tthu1501 - Friday, April 7, 2017 9:37 PMwe can calculate easter date without sql data like this When Is Easter Sunday
Heh.... yeah... do that for the previous 50 years and the next 50 years. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2017 at 3:47 pm
First, yes... I'm aware that the original script for this thread came out over 9 years ago. It is, however, a timeless problem and there is a rather serious optimization that can be had for those needing to Calculate Easter dates in T-SQL.
Second, I agree. No comments in the code, even for "black box" code, just isn't right. At least provide a reference for the basis of the code especially since it's not likely that you scienced out the method on your own. Speaking of that, here's a reference for the code.
https://en.wikipedia.org/wiki/Computus#Anonymous_Gregorian_algorithm
Even though the post by @tthu1501 wasn't very helpful when it comes to doing things in T-SQL, there is an implied solution and that is the concept of doing all the calculations for all the anticipated years and storing them in a Calendar table, which would reduce such problems to a simple lookup. But, what if the anticipated range of years that you need is incorrect and you need to extend it? What if you have a "DBA" or a set of company rules that states that you cannot use such reference tables (I know it sounds stupid, but there are such "DBA"s and places).
That, of course, brings us back to the use of functions. My general rule-of-thumb is that if the function contains the word "BEGIN/END" for anything other than a column name (which would be a naming problem itself), then you've probably written the function incorrectly. Please see the following article for why I say that and how to make scalar functions run quite a bit faster.
http://www.sqlservercentral.com/articles/T-SQL/91724/
Now, with the complexity of all of the methods/formulae that most people use, you might wonder how to create such an iSF (Inline Scalar Function as outlined in the previous link above). The answer, of course, is to pre-calculate the outcome of the formulae and see if there's a pattern. It turns out that there is such a pattern and, until the orbit of the moon changes substantially (it's moving away from the Earth at 3.78 Centimeters (1.5 Inches) per year) and it's pull on the Earth slows it down enough (about 4 hours per billion years) , the pattern will continue to exist at least for several more centuries. (Ref: https://www.google.com/?gws_rd=ssl#q=how+fast+is+the+moon+moving+away+from+the+earth ).
With that, the first I ever saw of such an optimization (it' sbased on the 19 year cycle for Easter Sunday) was by good friend and fellow SQL Server MVP, Peter "PESO" Larsson", at the following URL. I don't know if his optimization works for years prior to 1900 but I can't imagine it not. I will, however, leave that testing up to you because I haven't had the need to calculate Easter Sunday nor Good Friday (always occurs the Friday before) dates for anything in my particular line of work.
http://weblogs.sqlteam.com/peterl/archive/2010/09/08/fast-easter-day-function.aspx
The late, great Dwain Camps (miss you terribly, buddy) explains more about the 19 year cycle and the required "golden number" to come up with Easter Dates at the following article if you're interested in the actual math behind Peter's revelation. Dwain also included calculations for Good Friday, Ash Wednesday, and "Fat Tuesday", which are all dates based on a fixed timeframe from Easter Sunday.
http://www.sqlservercentral.com/blogs/dwainsql/2015/03/31/an-easter-sql/
{edit} Ugh! I've been checking across multiple different methods of calculating the date of Easter Sunday and I'm finding differences between the code of even people that I trust. With that, I've taken down the code that I previously posted because it was based on a collection of snippets based on those different methods.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2017 at 12:10 pm
Nicely put together there Santa.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply