SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 UK Calendar
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 02/09/2005 :  03:48:16  Show Profile
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  Show Profile
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 02/09/2005 :  06:35:07  Show Profile
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
Go to Top of Page

AndyB13
Aged Yak Warrior

United Kingdom
583 Posts

Posted - 02/09/2005 :  06:43:17  Show Profile
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 02/09/2005 :  06:46:59  Show Profile
May Day.. hehe
We just call it "1:a Maj" translates to: "1st of May".

Thanx again,

rockmoose
Go to Top of Page

AndyB13
Aged Yak Warrior

United Kingdom
583 Posts

Posted - 02/09/2005 :  07:12:10  Show Profile
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 02/09/2005 :  07:28:45  Show Profile
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
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2048 Posts

Posted - 06/14/2006 :  11:48:55  Show Profile
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

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

-----------

Oh, so they have internet on computers now!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/14/2006 :  12:17:49  Show Profile
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

http://www.dti.gov.uk/employment/bank-public-holidays/index.html
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
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2048 Posts

Posted - 06/15/2006 :  06:05:57  Show Profile
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/15/2006 :  10:09:17  Show Profile
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
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2048 Posts

Posted - 06/16/2006 :  03:08:15  Show Profile
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000