Datetime/public holidays

I need to be able to determine whether a particular
datetime value is a public holiday (in the uk) or not and
I cannot find a documented way of deteriming this. Please
could you advise me whether there is a function or method
of determining this in SQL 2000 Enterprise Edition?
If there is not could you advise me a robust method of
providing this functionality for developers (I am a design
DBA who works with a number of different development teams
and it would seem appropriate for everyone to use the same
method)?
Many thanks,
DavePublic holiday dates are not always determined by a fixed logic. You should
build your own calendar table for this and populate it in advance with as
much data as you need.
CREATE TABLE Calendar (caldate DATETIME NOT NULL PRIMARY KEY, workingday
CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')
INSERT INTO Calendar (caldate) VALUES ('20000101')
Populate it (this is 11 years worth of dates):
WHILE (SELECT MAX(caldate) FROM Calendar)<'20101231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Now update the non-working days:
UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')
A source I use for public holiday dates is: http://www.bank-holidays.com
--
David Portas
--
Please reply only to the newsgroup
--