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.thanxrockmoose

AndyB13
Aged Yak Warrior

583 Posts

 Posted - 2005-02-09 : 04:33:58 1st of JanEaster - Good Friday & Bank Holiday??? 1st Monday in MayLast Monday in MayLast Monday in AugustChristmas DayBoxing DayI 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 ScotlandAndy

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)ASBEGIN -- 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 @REND`rockmooserockmoose

AndyB13
Aged Yak Warrior

583 Posts

 Posted - 2005-02-09 : 06:43:17 These are what they are called (i think)New Years DayGood FridayEaster MondayMay Day -- 1st Monday in MayMay Bank HolidayAugust Bank HolidayChristmas DayBoxing DayGenerally though for May & August they are just known as Bank HolidayAndy

rockmoose
SQL Natt Alfen

3279 Posts

 Posted - 2005-02-09 : 06:46:59 May Day.. heheWe 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 elwoosI 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=9I know you have seen this topic before, since you posted on it.Create Date Table with UK & Easter bank holidayshttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49711CODO 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 NUMBER1 Feb 03 232 Feb 03 24...4 May 06 600 (for example)5 May 06 601Then 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_DATEhttp://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!