| Author |
 Topic  |
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
 Posted - 02/09/2005 : 03:48:16
|
Anyone has a UK calendar ?
Preferably an algorithm to calculate the holidays in the UK.
We need to know which days are bank holidays.
thanx
rockmoose |
|
|
AndyB13
Aged Yak Warrior
United Kingdom
583 Posts |
Posted - 02/09/2005 : 04:33:58
|
1st of Jan
Easter - Good Friday & Bank Holiday???
1st Monday in May
Last Monday in May
Last Monday in August
Christmas Day
Boxing Day
I created a date table using Excel for the next 20 years and manually changed Easter, then imported it into SQL.
NB: 2nd Jan is a Bank Holiday in Scotland
Andy |
 |
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 02/09/2005 : 06:35:07
|
Ok, thanx Andy!
Do the holidays have names of some kind ?
In sweden we have some fixed holidays (8 in all),
and a few that are calculated with the easter date as base.
Like so:
easterday - 2 = "Långfredag"
easterday + 1 = "Annandag påsk"
easterday + 39 = "Kristi Himmelfärd"
easterday + 50 = "Annandag Pingst" ( up to 2004, now removed :( )
So if we can decide which day is easterday, then we know all the holidays.
We do that with this function (maybe it's of some use to someone):
CREATE FUNCTION fnDLA_GetEasterdate(@year INT)
RETURNS CHAR (8)
AS
BEGIN
-- Easter date algorithm of Delambre
DECLARE @A INT,@B INT,@C INT,@D INT,@E INT,@F INT,@G INT,
@H INT,@I INT,@K INT,@L INT,@M INT,@O INT,@R INT
SET @A = @YEAR%19
SET @B = @YEAR / 100
SET @C = @YEAR%100
SET @D = @B / 4
SET @E = @B%4
SET @F = (@B + 8) / 25
SET @G = (@B - @F + 1) / 3
SET @H = ( 19 * @A + @B - @D - @G + 15)%30
SET @I = @C / 4
SET @K = @C%4
SET @L = (32 + 2 * @E + 2 * @I - @H - @K)%7
SET @M = (@A + 11 * @H + 22 * @L) / 451
SET @O = 22 + @H + @L - 7 * @M
IF @O > 31
BEGIN
SET @R = @O - 31 + 400 + @YEAR * 10000
END
ELSE
BEGIN
SET @R = @O + 300 + @YEAR * 10000
END
RETURN @R
END
rockmoose
rockmoose |
|
|
|
AndyB13
Aged Yak Warrior
United Kingdom
583 Posts |
Posted - 02/09/2005 : 06:43:17
|
These are what they are called (i think)
New Years Day
Good Friday
Easter Monday
May Day -- 1st Monday in May
May Bank Holiday
August Bank Holiday
Christmas Day
Boxing Day
Generally though for May & August they are just known as Bank Holiday
Andy |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 02/09/2005 : 06:46:59
|
May Day.. hehe
We just call it "1:a Maj" translates to: "1st of May".
Thanx again,
rockmoose |
|
|
|
AndyB13
Aged Yak Warrior
United Kingdom
583 Posts |
Posted - 02/09/2005 : 07:12:10
|
What... so you would call 2005-05-02 the 1st of May - how odd 
How does SQL handle that?
I'll get my coat..............
Andy |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 02/09/2005 : 07:28:45
|
Sorry, I mixed up everything, I need a holiday.....
So sleep deprived, and just work work :(
We don't have "May Day".
1st of May is yyyy0501 (=Labor Day I think).
rockmoose |
|
|
|
elwoos
Flowing Fount of Yak Knowledge
United Kingdom
2040 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
|
|
elwoos
Flowing Fount of Yak Knowledge
United Kingdom
2040 Posts |
Posted - 06/15/2006 : 06:05:57
|
thanks Michael.
I'm looking to create a "Count the number of working days between two dates function" i.e. not including weekends and Bank Holidays. I think I will slightly modify Andy's stuff in the other post and use a technique suggested by Arnold (though I can't find the link where he suggested it now). What I intend to do is delete all the non-working days from his table and then add an autonumber column. Then to get the number of working days you subtract the lower autonumber from the highest.
e.g.
DATE NUMBER
1 Feb 03 23
2 Feb 03 24
...
4 May 06 600 (for example)
5 May 06 601
Then the working days between 5 May 06 and 2 Feb 03 = 601 -24 viola!
steve
-----------
Oh, so they have internet on computers now! |
Edited by - elwoos on 06/15/2006 06:21:40 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 06/15/2006 : 10:09:17
|
Using the date table I posted on the link below, it's fairly easy to get the count of weekdays.
The only thing you would have to add is a left join to a Holiday table to filter holidays out, leaving you with a count of workdays.
Date Table Function F_TABLE_DATE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&whichpage=1
select
Weekdays = count(*)
from
dbo.F_TABLE_DATE ( '2003-02-03','2006-05-06')
where
-- Select Monday through Friday
[DAY_OF_WEEK] between 2 and 6
Results:
Weekdays
-----------
850
(1 row(s) affected)
CODO ERGO SUM |
|
|
|
elwoos
Flowing Fount of Yak Knowledge
United Kingdom
2040 Posts |
Posted - 06/16/2006 : 03:08:15
|
That's fantastic, thanks Michael. The function from AndyB in the link you posted will give me the bank holidays that I need which means I can add them to this.
steve
-----------
Oh, so they have internet on computers now! |
|
|
| |
Topic |
|
UK Calendar
Forum Locked
