UK Calendar - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

UK Calendar

Author

Topic

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-09 : 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

583 Posts

Posted - 2005-02-09 : 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

3279 Posts

Posted - 2005-02-09 : 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

583 Posts

Posted - 2005-02-09 : 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

3279 Posts

Posted - 2005-02-09 : 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

583 Posts

Posted - 2005-02-09 : 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

3279 Posts

Posted - 2005-02-09 : 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
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-06-14 : 11:48:55

I know this is an old topic but it is something I have to look at currently and I came across this for UK bank holidays

[url]http://www.dti.gov.uk/employment/bank-public-holidays/index.html[/url]
steve

-----------

Oh, so they have internet on computers now!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-14 : 12:17:49

quote:
Originally posted by elwoos

I know this is an old topic but it is something I have to look at currently and I came across this for UK bank holidays

[url]http://www.dti.gov.uk/employment/bank-public-holidays/index.html[/url]
steve

-----------

Oh, so they have internet on computers now!

If you need a reference for holidays in the UK, I have found this site to be very useful. It has has Clock, Calendar, Time Zone, and Holiday information for most of the world:
http://www.timeanddate.com/calendar/index.html?year=2006&country=9

I know you have seen this topic before, since you posted on it.

Create Date Table with UK & Easter bank holidays
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49711

CODO ERGO SUM

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-06-15 : 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!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-15 : 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
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-06-16 : 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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources