Calculating Easter

Thanks Aaron. Surprisingly Easter wasn't so much of a problem - I had a
formula for Easter that I was able to pretty easily convert to SQL. The
ones that I'm having fun with are Thanksgiving, Memorial Day, etc. that are
defined as "the fourth Thursday in November", "Last Monday in May", etc.
I'm hoping to find similarly easy functions for other holidays.
Just for the heck of it, here's the converted Easter Calculation formula I
converted. It works for years between 1900 and 2099. Thanks!
-- Set @.Year to the year you want to calculate for
DECLARE @.Year INT
DECLARE @.GoldenNumber INT
DECLARE @.EpactCalc INT
DECLARE @.PaschalDaysCalc INT
DECLARE @.PaschalWday INT
DECLARE @.NumOfDaysToSunday INT
DECLARE @.EasterMonth INT
DECLARE @.EasterDay INT
-- Define Year
SET @.Year = 2005
-- Golden Number of Year
SET @.GoldenNumber = @.Year % 19
-- Calculation based on Epact
SET @.EpactCalc = (24 + 19 * (@.GoldenNumber)) % 30
-- Number of days from March 21 to Paschal Full Moon
SET @.PaschalDaysCalc = @.EpactCalc - (@.EpactCalc / 28)
-- Calculate Wday of Paschal Full Moon (0 = Sunday, 1 = Monday)
SET @.PaschalWday = (@.Year + @.Year / 4 + @.PaschalDaysCalc - 13) % 7
-- Calculate Number of Days from March 21 to Sunday on or before Paschal
Full Moon
SET @.NumOfDaysToSunday = @.PaschalDaysCalc - @.PaschalWday
-- Calculate Easter Month
SET @.EasterMonth = 3 + (@.NumOfDaysToSunday + 40) / 44
-- Calculate Easter Day
SET @.EasterDay = @.NumOfDaysToSunday + 28 - 31 * (@.EasterMonth / 4)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE58AC64.2D8A%ten.xoc@.dnartreb.noraa...
>I should add this specific technique to http://www.aspfaq.com/2519
> Currently, my method determines when the clocks go forward/back, but it
> could certainly be enhanced for some others. Easter, however, will be a
> very complicated one, and I don't think you can solve it programmatically
> for all years. If it is only for 10 years, you might just want to do it
> manually...
>
>
> On 3/12/05 2:19 PM, in article koHYd.14007$Jj4.161@.fe12.lga, "Michael C#"
> <xyz@.abcdef.com> wrote:
>
>Michael,
Here's one for the 4th Thursday in November, for any specified Year @.Y
Cast(Str(@.Y,4)) + '11' +
Str(28 - (DatePart(dw, Str(@.Y,4) + '1101') + 1) % 7, 2)
as datetime)
You can modify this for any other ones that follow the same general format..
.
"Michael C#" wrote:

> Thanks Aaron. Surprisingly Easter wasn't so much of a problem - I had a
> formula for Easter that I was able to pretty easily convert to SQL. The
> ones that I'm having fun with are Thanksgiving, Memorial Day, etc. that ar
e
> defined as "the fourth Thursday in November", "Last Monday in May", etc.
> I'm hoping to find similarly easy functions for other holidays.
> Just for the heck of it, here's the converted Easter Calculation formula I
> converted. It works for years between 1900 and 2099. Thanks!
> -- Set @.Year to the year you want to calculate for
> DECLARE @.Year INT
> DECLARE @.GoldenNumber INT
> DECLARE @.EpactCalc INT
> DECLARE @.PaschalDaysCalc INT
> DECLARE @.PaschalWday INT
> DECLARE @.NumOfDaysToSunday INT
> DECLARE @.EasterMonth INT
> DECLARE @.EasterDay INT
> -- Define Year
> SET @.Year = 2005
> -- Golden Number of Year
> SET @.GoldenNumber = @.Year % 19
> -- Calculation based on Epact
> SET @.EpactCalc = (24 + 19 * (@.GoldenNumber)) % 30
> -- Number of days from March 21 to Paschal Full Moon
> SET @.PaschalDaysCalc = @.EpactCalc - (@.EpactCalc / 28)
> -- Calculate Wday of Paschal Full Moon (0 = Sunday, 1 = Monday)
> SET @.PaschalWday = (@.Year + @.Year / 4 + @.PaschalDaysCalc - 13) % 7
> -- Calculate Number of Days from March 21 to Sunday on or before Paschal
> Full Moon
> SET @.NumOfDaysToSunday = @.PaschalDaysCalc - @.PaschalWday
> -- Calculate Easter Month
> SET @.EasterMonth = 3 + (@.NumOfDaysToSunday + 40) / 44
> -- Calculate Easter Day
> SET @.EasterDay = @.NumOfDaysToSunday + 28 - 31 * (@.EasterMonth / 4)
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE58AC64.2D8A%ten.xoc@.dnartreb.noraa...
>
>|||Thanks, I was able to adapt the formula from your prior posted link to do
this. Had to modify it slightly to adjust the result, since it occasionally
pops up with negative numbers in the result, but I verified my results and
it works well.
Thanks
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:1A69E745-4CDF-48DB-990E-825962BA378B@.microsoft.com...
> Michael,
> Here's one for the 4th Thursday in November, for any specified Year @.Y
> Cast(Str(@.Y,4)) + '11' +
> Str(28 - (DatePart(dw, Str(@.Y,4) + '1101') + 1) % 7, 2)
> as datetime)
> You can modify this for any other ones that follow the same general
> format...
> "Michael C#" wrote:
>|||I've updated http://www.aspfaq.com/2519 to show how to populate the calendar
table with most holidays. Let me know if there are any glaring omissions
(however, comments from the peanut gallery about it catering to US holidays
> dev/null/ please).
:-)
A
On 3/12/05 4:47 PM, in article uDJYd.99$Mg7.24@.fe08.lga, "Michael C#"
<xyz@.abcdef.com> wrote:

> Thanks Aaron. Surprisingly Easter wasn't so much of a problem - I had a
> formula for Easter that I was able to pretty easily convert to SQL. The
> ones that I'm having fun with are Thanksgiving, Memorial Day, etc. that ar
e
> defined as "the fourth Thursday in November", "Last Monday in May", etc.
> I'm hoping to find similarly easy functions for other holidays.
> Just for the heck of it, here's the converted Easter Calculation formula I
> converted. It works for years between 1900 and 2099. Thanks!|||I tried your formula and got an error. I think the syntax of the CAST might
be off. I was able to create a SQL Version of the formulas provided at the
links you gave, although I did have to correct one error in the formula
(pertaining to negative values being returned.) Here's what I came up with
to find the Nth Occurrence of a Day in a Month:
-- Gets the Nth Occurrence of the Specified Day in a Month
-- Sunday = 0, Monday = 1, etc.
-- Define Variables
DECLARE @.Occurrence INT
DECLARE @.Day INT
DECLARE @.Month INT
DECLARE @.Year INT
DECLARE @.Date SMALLDATETIME
DECLARE @.DateCalc AS INT
DECLARE @.TempDate AS SMALLDATETIME
-- This will get the 4th Thursday of the month for
-- November 2008 (i.e., Thanksgiving)
-- Sets the Occurrence # we are looking for
SET @.Occurrence = 4
-- Set the Day we are looking for
SET @.Day = 4
-- Set the Month we are looking in
SET @.Month = 11
-- Set the Year
SET @.Year = 2008
-- Get the first day of the month
SET @.TempDate = CAST(@.Month AS VARCHAR(2)) + '/1/' + CAST(@.Year AS
VARCHAR(4))
-- Calculate the date for the occurrence
SET @.DateCalc = 1 + (@.Occurrence - 1) * 7 + (1 + (@.Day - DATEPART(dw,
@.TempDate) + 7) % 7)
-- Set the Date
SET @.Date = CAST(@.Month AS VARCHAR(2)) + '/' + CAST(@.DateCalc AS VARCHAR(2))
+ '/' + CAST(@.Year AS VARCHAR(4))
I also modified and fixed another formula to calculate the last occurrence
of a day of the month. Here's what I came up with for that:
-- Declare variables
DECLARE @.Month INT
DECLARE @.Year INT
DECLARE @.Day INT
DECLARE @.TempDate SMALLDATETIME
-- Determines the date of the last Monday
-- in May (Memorial Day)
SET @.Month = 5
SET @.Day = 1
SET @.Year = 2005
-- First we get the first day of the next month
-- For February, we loop back around to January
SET @.TempDate = CAST((@.Month % 12 + 1) AS VARCHAR(2)) + '/1/' + CAST(@.Year
AS VARCHAR(4))
DECLARE @.Date SMALLDATETIME
DECLARE @.LastDayOfMonth INT
DECLARE @.DateCalc INT
-- Now we subtract one day to get the last day
-- of the month specified
SET @.TempDate = @.TempDate - 1
SET @.LastDayOfMonth = DATEPART(d, @.TempDate)
-- Calculate date of the last occurrence of the specified day
SET @.DateCalc = @.LastDayOfMonth - ((DATEPART(dw, @.TempDate) - 1 - @.Day) + 7)
% 7
-- Convert to a date
SET @.Date = CAST(@.Month AS VARCHAR(2)) + '/' + CAST (@.DateCalc AS
VARCHAR(2)) + '/' + CAST (@.Year AS VARCHAR(4))
Thanks!
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:1A69E745-4CDF-48DB-990E-825962BA378B@.microsoft.com...
> Michael,
> Here's one for the 4th Thursday in November, for any specified Year @.Y
> Cast(Str(@.Y,4)) + '11' +
> Str(28 - (DatePart(dw, Str(@.Y,4) + '1101') + 1) % 7, 2)
> as datetime)
> You can modify this for any other ones that follow the same general
> format...
> "Michael C#" wrote:
>|||Ooops. In the comments it says for "February we loop back around to
January"; that's actually in December. Thanks|||Unfortunately, your formula depends on the current datefirst setting,
since it uses the numerical wday.
Here is what I think is a datefirst-independent formula for
the 4th Thursday in November, valid for any SQL Server
year except for 1753.
dateadd(d,datediff(d,'17541128',str(@.Y)+
'1128')/7*7, '17541128')
The limitation shouldn't be an issue if this is needed for U.S.
Thanksgiving, since it wasn't established as the fourth Thursday
in November until 1941.
Steve Kass
Drew University
CBretana wrote:
>Michael,
>Here's one for the 4th Thursday in November, for any specified Year @.Y
>Cast(Str(@.Y,4)) + '11' +
> Str(28 - (DatePart(dw, Str(@.Y,4) + '1101') + 1) % 7, 2)
> as datetime)
>You can modify this for any other ones that follow the same general format.
.
>"Michael C#" wrote:
>
>