Here you go, two functions - the comments should give you enough info to use them, or use the concept in another application. The first function returns a datetime corresponding to the date of Easter Sunday for the year input (this is required for the second function). The second function returns a table of datetimes with all bank holiday dates for the current year.

The main difficulty is Easter, but if you don't mind being restricted to 1900-2099 (which should be ample for most applications) then the Carter algorithm below should help.

if exists (select * from sysobjects where [name] = 'Easter' and [type] = 'FN')

drop function dbo.Easter

go

create function Easter (@Y smallint)

returns datetime

as

begin

/*

EASTER SUNDAY FUNCTION

Calculates date of Easter Sunday based on the Carter algorithm.

This one works for 1900-2099. More complex algorithms exist,

but are deemed unnecessary for this application (for efficiency).

Sourced from National Maritime Museum - http://www.nmm.ac.uk/

Algorithm instructions from the Museum are included as comments.

*/

declare @D tinyint, @E tinyint, @Q tinyint

declare @ret datetime

-- Calculate D = 225 - 11(Y MOD 19)

select @D = 225 - (11 * (@Y % 19))

-- If D is greater than 50 then subtract multiples of 30 until the

-- resulting new value of D is less than 51

while @D > 50

select @D = @D - 30

-- If D is greater than 48 subtract 1 from it

if @D > 48

select @D = @D - 1

-- Calculate E = (Y + (Y/4) + D + 1) MOD 7. (NB Integer part of (Y/4))

select @E = (@Y + floor(@Y/4) + @D + 1) % 7

-- Calculate Q = D + 7 - E

select @Q = @D + 7 - @E

-- If Q is less than 32 then Easter is in March. If Q is greater than 31,

-- then Q-31 is its date in April.

if @Q < 32

select @ret = convert(datetime,'03/'+convert(varchar,@Q)+'/'+convert(varchar,@Y))

else

select @ret = convert(datetime,'04/'+convert(varchar,(@Q-31))+'/'+convert(varchar,@Y))

return(@ret)

end

go

-- Function to calculate bank holidays

if exists (select * from sysobjects where [name] = 'BankHolidays' and [type] = 'TF')

drop function dbo.BankHolidays

go

create function dbo.BankHolidays (@YearIn smallint)

returns @ret table (BHDate datetime)

as

begin

/*

ENGLAND AND WALES BANK HOLIDAY DATES 1978 - 2099

AUTOMATIC CALCULATION

Restricted by Easter Sunday algorithm to 1900 - 2099

Can be set to work for any dates between 1900 - 2099 but:

- Current Bank Holidays were introduced in 1971 by the

Banking and Financial Dealings Act 1971, so date before

then will be inaccurate

- New Year's Day was introduced in 1974*

- Early May Bank Holiday was introduced in 1978*

- The script therefore does not account for bank holiday dates

introduced by the Bank Holiday Act of 1871 which ran until 1970.

*not included in Act so declared by Royal Proclamation each year

Developed by Paul Cresham

© 2004 Orchard Information Systems Limited

*/

declare @Holiday datetime

declare @Saturday tinyint, @Sunday tinyint, @Monday tinyint

select @Sunday = 8 - @@datefirst

select @Saturday = ( (@Sunday+5) % 7) + 1

select @Monday = ( (@Sunday) % 7) + 1

-- No 1: New Year's Day

-- 1 January or next weekday if it falls on a Saturday or Sunday

select @Holiday = convert(datetime,'01/01/'+convert(varchar,@YearIn))

while (datepart(dw,@Holiday) = @Saturday) Or (datepart(dw,@Holiday) = @Sunday)

select @Holiday = dateadd(day, 1, @Holiday)

insert @ret values (@Holiday)

-- No 2: Good Friday

-- 2 days before Easter Sunday

select @Holiday = dbo.Easter(@YearIn)

select @Holiday = dateadd(day,-2,@Holiday)

insert @ret values (@Holiday)

-- No 3: Easter Monday

-- 1 day after Easter Sunday (or 3 days after date currently held for Good Friday)

select @Holiday = dateadd(day,3,@Holiday)

insert @ret values (@Holiday)

-- No 4: May Day

-- 1st Monday in May

select @Holiday = convert(datetime,'05/01/'+convert(varchar,@YearIn))

while datepart(dw,@Holiday) != @Monday

select @Holiday = dateadd(day,1,@Holiday)

insert @ret values (@Holiday)

-- No 5: Spring Bank Holiday

-- Last Monday in May

select @Holiday = convert(datetime,'05/31/'+convert(varchar,@YearIn))

while datepart(dw,@Holiday) != @Monday

select @Holiday = dateadd(day,-1,@Holiday)

insert @ret values (@Holiday)

-- No 6: Late Summer Bank Holiday

-- Last Monday in August

select @Holiday = convert(datetime,'08/31/'+convert(varchar,@YearIn))

while datepart(dw,@Holiday) != @Monday

select @Holiday = dateadd(day,-1,@Holiday)

insert @ret values (@Holiday)

-- No 7: Christmas Day

-- 25 December, or next Monday in lieu if falls on Saturday or Sunday

select @Holiday = convert(datetime,'12/25/'+convert(varchar,@YearIn))

while (datepart(dw,@Holiday) = @Saturday) Or (datepart(dw,@Holiday) = @Sunday)

select @Holiday = dateadd(day,1,@Holiday)

insert @ret values (@Holiday)

-- No 8: Boxing Day

-- 26 December, or next Monday in lieu if this falls on a Saturday or

-- Tuesday if it falls on a Sunday

select @Holiday = dateadd(day,1,@Holiday)

while (datepart(dw,@Holiday) = @Saturday) Or (datepart(dw,@Holiday) = @Sunday)

select @Holiday = dateadd(day,1,@Holiday)

insert @ret values (@Holiday)

return

end

go

___________________________________________________________

This post is supplied "as is" and confers no rights and is without any warranties, express or implied.