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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
2052 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
2052 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
2052 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.31 seconds. Powered By: Snitz Forums 2000