Calculating Holidays

Hi all,
I want to calculate holidays for the next 10 years and store them in a
table. Unfortunately a lot of holidays are defined as "the <nth> day of
<Month>." Has anyone done this already, or can you point me to a website
that describes how to do this before I re-invent the wheel?
ThanksI 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:

> Hi all,
> I want to calculate holidays for the next 10 years and store them in a
> table. Unfortunately a lot of holidays are defined as "the <nth> day of
> <Month>." Has anyone done this already, or can you point me to a website
> that describes how to do this before I re-invent the wheel?
> Thanks
>|||http://www.opm.gov/fedhol/
Algorithms: at
http://www.smart.net/~mmontes/ushols.html
International:
http://www.tyzo.com/tools/holidays.html
"Michael C#" wrote:

> Hi all,
> I want to calculate holidays for the next 10 years and store them in a
> table. Unfortunately a lot of holidays are defined as "the <nth> day of
> <Month>." Has anyone done this already, or can you point me to a website
> that describes how to do this before I re-invent the wheel?
> Thanks
>
>|||"Michael C#" <xyz@.abcdef.com> wrote in message
news:koHYd.14007$Jj4.161@.fe12.lga...
> Hi all,
> I want to calculate holidays for the next 10 years and store them in a tab
le.
> Unfortunately a lot of holidays are defined as "the <nth> day of <Month>."
> Has anyone done this already, or can you point me to a website that descri
bes
> how to do this before I re-invent the wheel?
> Thanks
Something as simple as the enumeration of all dates over the next
10 years should be helpful.
CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
-- Nonnegative integers to some suitable upper bound
CREATE VIEW N (i)
AS
SELECT Ones.d + 10*Tens.d + 100*Hundreds.d + 1000*Thousands.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CROSS JOIN
Digits AS Thousands
CREATE VIEW Dates (d, month, day, year, wday)
AS
SELECT D.d,
MONTH(D.d),
DAY(D.d),
YEAR(D.d),
DATEPART(WEEKDAY, D.d)
FROM (SELECT CAST('20050101' AS DATETIME) + N.i
FROM N) AS D(d)
-- Thanksgiving in the US is the 4th Thursday in November
CREATE VIEW Thanksgiving (d)
AS
SELECT d
FROM Dates AS D1
WHERE month = 11 AND
wday = 5 AND
4 = (SELECT COUNT(*)
FROM Dates AS D2
WHERE month = 11 AND
wday = 5 AND
D1.year = D2.year AND
D2.d <= D1.d)
SELECT d
FROM Thanksgiving
ORDER BY d
-- Memorial Day in the US is the last Monday in May
CREATE VIEW MemorialDay (d)
AS
SELECT d
FROM Dates AS D1
WHERE month = 5 AND
wday = 2 AND
NOT EXISTS (SELECT *
FROM Dates AS D2
WHERE month = 5 AND
wday = 2 AND
D1.year = D2.year
AND
D2.d > D1.d)
SELECT d
FROM MemorialDay
ORDER BY d
JAG|||Michael C# wrote:
> Hi all,
> I want to calculate holidays for the next 10 years and store them in a
> table. Unfortunately a lot of holidays are defined as "the <nth> day of
> <Month>." Has anyone done this already, or can you point me to a website
> that describes how to do this before I re-invent the wheel?
There was a dicussion in the comp.databases.ms-access newsgroup about
this in Oct, 2004. One poster came up w/ some VBA functions that he
claims gets the date for the "X day in the N month." Here is the thread
in Google
85bd8a805473" target="_blank">http://groups-beta.google.com/group...>
85bd8a805473
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Thank you, I was able to adapt the formula at the second link to calculate
the holidays like Labor Day that are defined as the "First Monday in
September", etc. Now I just need to modify to determine Memorial Day,
defined as the "Last Monday in May", and any others like it.
Thanks
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:D4CA4A86-3324-4549-AF0B-A2BCD27DC02B@.microsoft.com...
> http://www.opm.gov/fedhol/
> Algorithms: at
> http://www.smart.net/~mmontes/ushols.html
> International:
> http://www.tyzo.com/tools/holidays.html
> "Michael C#" wrote:
>|||Thanks, I actually found a formula I was able to modify to get this value.
Thanks!
"MGFoster" <me@.privacy.com> wrote in message
news:O1KYd.8677$cN6.2566@.newsread1.news.pas.earthlink.net...
> Michael C# wrote:
> There was a dicussion in the comp.databases.ms-access newsgroup about this
> in Oct, 2004. One poster came up w/ some VBA functions that he claims
> gets the date for the "X day in the N month." Here is the thread in Google
> 7285bd8a805473" target="_blank">http://groups-beta.google.com/group...
7285bd8a805473
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)|||Thanks, I've actually found a solution, but I'm going to test yours as well.
It looks very interesting! Thanks
"John Gilson" <jagREMOVE@.acmMUNGE.org> wrote in message
news:UNIYd.32150$534.5183@.twister.nyc.rr.com...
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:koHYd.14007$Jj4.161@.fe12.lga...
> Something as simple as the enumeration of all dates over the next
> 10 years should be helpful.
> CREATE VIEW Digits (d)
> AS
> SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
> SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
> SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
> SELECT 9
> -- Nonnegative integers to some suitable upper bound
> CREATE VIEW N (i)
> AS
> SELECT Ones.d + 10*Tens.d + 100*Hundreds.d + 1000*Thousands.d
> FROM Digits AS Ones
> CROSS JOIN
> Digits AS Tens
> CROSS JOIN
> Digits AS Hundreds
> CROSS JOIN
> Digits AS Thousands
> CREATE VIEW Dates (d, month, day, year, wday)
> AS
> SELECT D.d,
> MONTH(D.d),
> DAY(D.d),
> YEAR(D.d),
> DATEPART(WEEKDAY, D.d)
> FROM (SELECT CAST('20050101' AS DATETIME) + N.i
> FROM N) AS D(d)
> -- Thanksgiving in the US is the 4th Thursday in November
> CREATE VIEW Thanksgiving (d)
> AS
> SELECT d
> FROM Dates AS D1
> WHERE month = 11 AND
> wday = 5 AND
> 4 = (SELECT COUNT(*)
> FROM Dates AS D2
> WHERE month = 11 AND
> wday = 5 AND
> D1.year = D2.year AND
> D2.d <= D1.d)
> SELECT d
> FROM Thanksgiving
> ORDER BY d
> -- Memorial Day in the US is the last Monday in May
> CREATE VIEW MemorialDay (d)
> AS
> SELECT d
> FROM Dates AS D1
> WHERE month = 5 AND
> wday = 2 AND
> NOT EXISTS (SELECT *
> FROM Dates AS D2
> WHERE month = 5 AND
> wday = 2
> AND
> D1.year =
> D2.year AND
> D2.d > D1.d)
> SELECT d
> FROM MemorialDay
> ORDER BY d
> --
> JAG
>|||Here's an oblivious version (only sketchily tested) that works for all
years from 1761 forward.
-- Finds the @.n-th @.wd-day in month,year @.m,@.y
declare @. char(168)
set @. = -- required data
'012394594501233450128128345050128348'+
'345012128345045012832834501012834534'+
'501282834501'
-- example: 2-nd Monday in March, 2005
declare @.m int set @.m = 3
declare @.wd int set @.wd = 1 -- Monday = 1, not dependent on datefirst
declare @.n int set @.n = 2
declare @.y int set @.y = 2005
select @.wd+7*@.m-7,substring(@.,@.wd+7*@.m-7,1),
str(1753+substring(@.,@.wd+7*@.m-7,1))+right(100+@.m,2)+'01',
dateadd(
d,
datediff(
d,
str(1753+substring(@.,@.wd+7*@.m-7,1))+right(100+@.m,2)+'01',
str(@.y)+right(100+@.m,2)+'01')/7*7+7*@.n,
str(1753+substring(@.,@.wd+7*@.m-7,1))+right(100+@.m,2)+'01')
Steve Kass
Drew University
MGFoster wrote:

> Michael C# wrote:
>
>
> There was a dicussion in the comp.databases.ms-access newsgroup about
> this in Oct, 2004. One poster came up w/ some VBA functions that he
> claims gets the date for the "X day in the N month." Here is the
> thread in Google
> 7285bd8a805473" target="_blank">http://groups-beta.google.com/group...
7285bd8a805473
>|||I have no idea what this is, but I'm curiously aroused LOFL. I'm definitely
going to take a look at this one.
"Steve Kass" <skass@.drew.edu> wrote in message
news:etafDcGKFHA.580@.TK2MSFTNGP15.phx.gbl...
> Here's an oblivious version (only sketchily tested) that works for all
> years from 1761 forward.
> -- Finds the @.n-th @.wd-day in month,year @.m,@.y
> declare @. char(168)
> set @. = -- required data
> '012394594501233450128128345050128348'+
> '345012128345045012832834501012834534'+
> '501282834501'
> -- example: 2-nd Monday in March, 2005
> declare @.m int set @.m = 3
> declare @.wd int set @.wd = 1 -- Monday = 1, not dependent on datefirst
> declare @.n int set @.n = 2
> declare @.y int set @.y = 2005
> select @.wd+7*@.m-7,substring(@.,@.wd+7*@.m-7,1),
> str(1753+substring(@.,@.wd+7*@.m-7,1))+right(100+@.m,2)+'01',
> dateadd(
> d,
> datediff(
> d,
> str(1753+substring(@.,@.wd+7*@.m-7,1))+right(100+@.m,2)+'01',
> str(@.y)+right(100+@.m,2)+'01')/7*7+7*@.n,
> str(1753+substring(@.,@.wd+7*@.m-7,1))+right(100+@.m,2)+'01')
> Steve Kass
> Drew University
> MGFoster wrote:
>