Re: SQL Holiday Calculations

Yvette,

> I just saw an email that you sent a while back:
> On Tue, 18 Sep 2001, Josh Berkus wrote: > Folks, > > I'm spec'ing a
> calendar
> app for PostgreSQL, and was wondering if anyone > had already solved
> the
> following problem: > > How can I calculate the dates of American
> holidays? >
> > Obviously, Christmas & New Year's are easy. As is July 4. > >
> However,
> Thanksgiving is the last Thursday in November, unless the month >
> ends on a
> Thursday or Friday, in which case it is the next-to-last. > Memorial
> Day and
> Labor Day are simpler, but also use the "First or Last > Monday in x
> month"
> idea. > > I was wondering if anyone had already figured out these
> calculations, in > any language (SQL would be terrific). > > Thanks!

> Can you tell me if you ever received a reply or figured out how to do
> the
> Calculations in SQL.

No, actually, In fact, I got an e-mail from Joe Celko (I think) where
he points out that any holiday calculations are undependable because
state legislatures and Congress change holiday schedules all the time.
In fact, the only reliable guide is an almanac.

You could fairly easily calculate, say, "The Second Monday In February"
using Postgres' date functions, as:

select '2002-02-01'::DATE + (extract(dow from '2002-02-01'::DATE) +
5::INT)::INT;

However, President's day is not *always* the second monday of February
in all states.

I was able to lift a list of the next 5 years of US National holidays
from a vendor application. When I get it cleaned up, I'll post it
somewhere, probably techdocs.

-Josh Berkus