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!
 All Forums  Old Forums  CLOSED - General SQL Server  UK Calendar Forum Locked  Printer Friendly
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.thanxrockmoose

AndyB13
Aged Yak Warrior

United Kingdom
583 Posts

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

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```rockmooserockmoose

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

Sweden
3279 Posts

 Posted - 02/09/2005 :  06:46:59 May Day.. heheWe 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
2052 Posts

 Posted - 06/14/2006 :  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 holidayshttp://www.dti.gov.uk/employment/bank-public-holidays/index.htmlsteve-----------Oh, so they have internet on computers now!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

 Posted - 06/14/2006 :  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 holidayshttp://www.dti.gov.uk/employment/bank-public-holidays/index.htmlsteve-----------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
Flowing Fount of Yak Knowledge

United Kingdom
2052 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 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! Edited by - elwoos on 06/15/2006 06:21:40

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 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_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
Flowing Fount of Yak Knowledge

United Kingdom
2052 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
 Forum Locked  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC