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
 General SQL Server Forums
 Script Library
 Create Date Table with UK & Easter bank holidays
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AndyB13
Aged Yak Warrior

United Kingdom
583 Posts

Posted - 05/12/2005 :  17:30:15  Show Profile  Reply with Quote
Also includes ISOWeek & Weekends

Dont know whether this is of any use to anyone or it has been done before but there are a lot of posts on here regarding date calculation issues & usually the most straight forward answer is to compare against a table of dates.

So while looking at Bretts blog and another post on here, i thought i'd post this on here
http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49698

Special thanks to rockmoose & BOL (as always)

Edit: It also moves bank holidays to the following Monday (and Tuesday - xmas) if the bank holiday(s) falls on the weekend

SET DATEFIRST 1
SET NOCOUNT ON
GO

--Create ISO week Function (thanks BOL)
CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= DATEPART(wk,@DATE)+1
      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0) 
      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND 
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END
GO
--END ISOweek

--CREATE Easter algorithm function 
--Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689)
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
GO
--END fnDLA_GetEasterdate

--Create the table
CREATE TABLE MyDateTable
(
FullDate datetime NOT NULL CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED, 
Period int, 
ISOWeek int, 
WorkingDay varchar(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y'
)
GO
--End table create

--Populate table with required dates
DECLARE @DateFrom datetime, @DateTo datetime, @Period int
SET @DateFrom = CONVERT(datetime,'20000101') --yyyymmdd (1st Jan 2000) amend as required
SET @DateTo = CONVERT(datetime,'20991231') --yyyymmdd (31st Dec 2099) amend as required
WHILE @DateFrom <= @DateTo
BEGIN
	SET @Period = CONVERT(int,LEFT(CONVERT(varchar(10),@DateFrom,112),6))
		INSERT MyDateTable(FullDate, Period, ISOWeek)
		SELECT @DateFrom, @Period, dbo.ISOweek(@DateFrom)
	SET @DateFrom = DATEADD(dd,+1,@DateFrom)
END
GO
--End population


/* Start of WorkingDays UPDATE */
UPDATE MyDateTable
SET WorkingDay = 'B' --B = Bank Holiday
--------------------------------EASTER---------------------------------------------
WHERE FullDate = DATEADD(dd,-2,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Good Friday
	OR FullDate = DATEADD(dd,+1,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Easter Monday
GO

UPDATE MyDateTable
SET WorkingDay = 'B'
--------------------------------NEW YEAR-------------------------------------------
WHERE FullDate IN (SELECT MIN(FullDate) FROM MyDateTable 
			WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dw,FullDate) NOT IN (6,7)
			GROUP BY DATEPART(yy,FullDate))
---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------
	OR FullDate IN (SELECT MIN(FullDate) FROM MyDateTable 
			WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
			GROUP BY DATEPART(yy,FullDate))
	OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable 
			WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
			GROUP BY DATEPART(yy,FullDate))
--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------
	OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable 
			WHERE DATEPART(mm,FullDate) = 8 AND DATEPART(dw,FullDate) = 1
			GROUP BY DATEPART(yy,FullDate))
--------------------XMAS(Move to next working day if on Sat/Sun)--------------------
	OR FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN
					DATEADD(dd,+2,FullDate) ELSE FullDate END
			FROM MyDateTable 
			WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25,26))
GO

---------------------------------------WEEKENDS--------------------------------------
UPDATE MyDateTable
SET WorkingDay = 'N'
WHERE DATEPART(dw,FullDate) IN (6,7)
GO
/* End of WorkingDays UPDATE */

--SELECT * FROM MyDateTable ORDER BY 1
DROP FUNCTION fnDLA_GetEasterdate
DROP FUNCTION ISOweek
--DROP TABLE MyDateTable

SET NOCOUNT OFF


Andy

Beauty is in the eyes of the beerholder

Edited by - AndyB13 on 05/12/2005 17:40:37

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2052 Posts

Posted - 05/20/2005 :  03:44:15  Show Profile  Reply with Quote
Andy you're a star I think I may owe you a few beers after this - you like Sam Smiths?


steve

A sarcasm detector, what a great idea.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 05/20/2005 :  14:18:18  Show Profile  Reply with Quote
Great Andy!
Why didn't You tell us You were going to do this ?
You could have gotten some
One of the DBA's implemented a UK calender, (after Your info on the UK holidays some time ago).

rockmoose
Go to Top of Page

AndyB13
Aged Yak Warrior

United Kingdom
583 Posts

Posted - 05/24/2005 :  17:34:21  Show Profile  Reply with Quote
quote:
Originally posted by elwoos

Andy you're a star I think I may owe you a few beers after this - you like Sam Smiths?



Thanks, nah i'm a puff
More of a lager (beer US) man myself - WIFE BEATER (Stella Artois)



quote:
Originally posted by rockmoose

Great Andy!
Why didn't You tell us You were going to do this ?
You could have gotten some
One of the DBA's implemented a UK calender, (after Your info on the UK holidays some time ago).



Thanks rockmoose, i actually did this not long after that post but i forgot to post it on here. It was only when i was reading that other post & Bretts log that i remembered it.


Glad its of some use.
There is probably a more logical/quicker approach to my example but given that the script would only need to be run once (30 seconds/100 years of dates) I didnt really see the point.

Andy

Beauty is in the eyes of the beerholder

Edited by - AndyB13 on 05/24/2005 17:40:53
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 12/05/2006 :  10:34:43  Show Profile  Reply with Quote
Now that the beer barrel is empty and the celebrations are over...

So as far as I can see, this deals with:
New Year's Day
New Year's Day Holiday
Good Friday
Easter Sunday
Easter Monday
Early May Bank Holiday
Christmas Day
Boxing Day

What about:
Early May Bank Holiday
Spring Bank Holiday
Summer Bank Holiday
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000