SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Holiday Calendar Generator


Holiday Calendar Generator

Author
Message
Wookie DBA
Wookie DBA
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 205

Hello all.

I would like to create a calendar table within SQL Server, include holidays and populate it for a few years. Does anyone have a set of statements they have used before for something like this?


SSM-140546
SSM-140546
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 1

I made a monthly calender...c if it helps.u can probably build further on this.omit the parts which r necessary in ur case..


CREATE PROCEDURE MonthlyCalendar
@year int,
@month int,
@program_id int
as
begin
declare @fdm int
declare @fdmstr varchar(10)
declare @datestr varchar(10)
declare @date1 datetime
declare @M1 datetime
declare @M2 datetime
declare @M3 datetime
declare @M4 datetime
declare @M5 datetime
declare @T1 datetime
declare @T2 datetime
declare @T3 datetime
declare @T4 datetime
declare @T5 datetime
declare @W1 datetime
declare @W2 datetime
declare @W3 datetime
declare @W4 datetime
declare @W5 datetime
declare @TH1 datetime
declare @TH2 datetime
declare @TH3 datetime
declare @TH4 datetime
declare @TH5 datetime
declare @F1 datetime
declare @F2 datetime
declare @F3 datetime
declare @F4 datetime
declare @F5 datetime
declare @S1 datetime
declare @S2 datetime
declare @S3 datetime
declare @S4 datetime
declare @S5 datetime
declare @Su1 datetime
declare @Su2 datetime
declare @Su3 datetime
declare @Su4 datetime
declare @Su5 datetime
declare @AM1 int
declare @AM2 int
declare @AM3 int
declare @AM4 int
declare @AM5 int
declare @AT1 int
declare @AT2 int
declare @AT3 int
declare @AT4 int
declare @AT5 int
declare @AW1 int
declare @AW2 int
declare @AW3 int
declare @AW4 int
declare @AW5 int
declare @ATH1 int
declare @ATH2 int
declare @ATH3 int
declare @ATH4 int
declare @ATH5 int
declare @AF1 int
declare @AF2 int
declare @AF3 int
declare @AF4 int
declare @AF5 int
set @datestr=cast(@month as varchar(2))+'/01/'+cast(@year as varchar(4))
set @date1=convert(datetime,@datestr,101)
set @fdm=datepart(dw,@date1)
set @fdmstr=datename(dw,@date1)
--print @date1
--print @fdmstr
if @fdmstr='Monday'
begin
set @M1 =@date1
set @T1 =dateadd(dd,1,@date1)
set @W1 =dateadd(dd,2,@date1)
set @TH1 =dateadd(dd,3,@date1)
set @F1 =dateadd(dd,4,@date1)
set @S1 =dateadd(dd,5,@date1)
set @Su1 =dateadd(dd,6,@date1)
end
if @fdmstr='Tuesday'
begin
set @M1 =NULL
set @T1 =@date1
set @W1 =dateadd(dd,1,@date1)
set @TH1 =dateadd(dd,2,@date1)
set @F1 =dateadd(dd,3,@date1)
set @S1 =dateadd(dd,4,@date1)
set @Su1 =dateadd(dd,5,@date1)
end
if @fdmstr='Wednesday'
begin
set @M1 =NULL
set @T1 =NULL
set @W1 =@date1
set @TH1 =dateadd(dd,1,@date1)
set @F1 =dateadd(dd,2,@date1)
set @S1 =dateadd(dd,3,@date1)
set @Su1 =dateadd(dd,4,@date1)
end
if @fdmstr='Thursday'
begin
set @M1 =NULL
set @T1 =NULL
set @W1 =NULL
set @TH1 =@date1
set @F1 =dateadd(dd,1,@date1)
set @S1 =dateadd(dd,2,@date1)
set @Su1 =dateadd(dd,3,@date1)
end
if @fdmstr='Friday'
begin
set @M1 =NULL
set @T1 =NULL
set @W1 =NULL
set @TH1 =NULL
set @F1 =@date1
set @S1 =dateadd(dd,1,@date1)
set @Su1 =dateadd(dd,2,@date1)
end
if @fdmstr='Saturday'
begin
set @M1 =NULL
set @T1 =NULL
set @W1 =NULL
set @TH1 =NULL
set @F1 =NULL
set @S1 =@date1
set @Su1 =dateadd(dd,1,@date1)
end
if @fdmstr='Sunday'
begin
set @M1 =NULL
set @T1 =NULL
set @W1 =NULL
set @TH1 =NULL
set @F1 =NULL
set @S1 =NULL
set @Su1 =@date1
end
SET @M2=DATEADD(DD,1,@SU1)
SET @M3=DATEADD(DD,7,@M2)
SET @M4=DATEADD(DD,7,@M3)
SET @M5=DATEADD(DD,7,@M4)
IF Month(@M5) <> @month
Set @M5=Null
SET @T2=DATEADD(DD,1,@M2)
SET @T3=DATEADD(DD,7,@T2)
SET @T4=DATEADD(DD,7,@T3)
SET @T5=DATEADD(DD,7,@T4)
IF Month(@T5) <> @month
Set @T5=Null
SET @W2=DATEADD(DD,1,@T2)
SET @W3=DATEADD(DD,7,@W2)
SET @W4=DATEADD(DD,7,@W3)
SET @W5=DATEADD(DD,7,@W4)
IF Month(@W5) <> @month
Set @W5=Null
SET @TH2=DATEADD(DD,1,@W2)
SET @TH3=DATEADD(DD,7,@TH2)
SET @TH4=DATEADD(DD,7,@TH3)
SET @TH5=DATEADD(DD,7,@TH4)
IF Month(@TH5) <> @month
Set @TH5=Null
SET @F2=DATEADD(DD,1,@TH2)
SET @F3=DATEADD(DD,7,@F2)
SET @F4=DATEADD(DD,7,@F3)
SET @F5=DATEADD(DD,7,@F4)
IF Month(@F5) <> @month
Set @F5=Null
select @AM1=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m1)=0 )
select @AM2=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m2)=0 )
select @AM3=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m3)=0 )
select @AM4=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m4)=0 )
select @AM5=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m5)=0 )
select @AT1=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t1)=0 )
select @AT2=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t2)=0 )
select @AT3=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t3)=0 )
select @AT4=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t4)=0 )
select @AT5=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t5)=0 )
select @AW1=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w1)=0 )
select @AW2=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w2)=0 )
select @AW3=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w3)=0 )
select @AW4=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w4)=0 )
select @AW5=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w5)=0 )
select @ATH1=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th1)=0 )
select @ATH2=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th2)=0 )
select @ATH3=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th3)=0 )
select @ATH4=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th4)=0 )
select @ATH5=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th5)=0 )
select @AF1=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f1)=0 )
select @AF2=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f2)=0 )
select @AF3=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f3)=0 )
select @AF4=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f4)=0 )
select @AF5=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f5)=0 )
SELECT convert(char(7),@M1,6) AS 'M1',convert(char(7),@M2,6) AS 'M2',convert(char(7),@M3,6) AS 'M3',convert(char(7),@M4,6) AS 'M4',convert(char(7),@M5,6) AS 'M5',
convert(char(7),@T1,6) AS 'T1',convert(char(7),@T2,6) AS 'T2',convert(char(7),@T3,6) AS 'T3',convert(char(7),@T4,6) AS 'T4',convert(char(7),@T5,6) AS 'T5',
convert(char(7),@W1,6) AS 'W1',convert(char(7),@W2,6) AS 'W2',convert(char(7),@W3,6) AS 'W3',convert(char(7),@W4,6) AS 'W4',convert(char(7),@W5,6) AS 'W5',
convert(char(7),@TH1,6) AS 'TH1',convert(char(7),@TH2,6) AS 'TH2',convert(char(7),@TH3,6) AS 'TH3',convert(char(7),@TH4,6) AS 'TH4',convert(char(7),@TH5,6) AS 'TH5',
convert(char(7),@F1,6) AS 'F1',convert(char(7),@F2,6) AS 'F2',convert(char(7),@F3,6) AS 'F3',convert(char(7),@F4,6) AS 'F4',convert(char(7),@F5,6) AS 'F5',
@AM1 AS 'AM1',@AM2 AS 'AM2',@AM3 AS 'AM3',@AM4 AS 'AM4',@AM5 AS 'AM5',
@AT1 AS 'AT1',@AT2 AS 'AT2',@AT3 AS 'AT3',@AT4 AS 'AT4',@AT5 AS 'AT5',
@AW1 AS 'AW1',@AW2 AS 'AW2',@AW3 AS 'AW3',@AW4 AS 'AW4',@AW5 AS 'AW5',
@ATH1 AS 'ATH1',@ATH2 AS 'ATH2',@ATH3 AS 'ATH3',@ATH4 AS 'ATH4',@ATH5 AS 'ATH5',
@AF1 AS 'AF1',@AF2 AS 'AF2',@AF3 AS 'AF3',@AF4 AS 'AF4',@AF5 AS 'AF5'
END

GO

-Sudha





thanx...,

SSM
Joe Booth
Joe Booth
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 4

See if the following function does the trick for you

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FloatingDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FloatingDate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday_List]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Holiday_List]
GO

---------------------------------------------------------------------------------
-- Function to return the X occurence of a particular weekday with a month/year
---------------------------------------------------------------------------------
CREATE FUNCTION dbo.FloatingDate(@Occur INT,@WeekDay INT,@Month INT,@Year INT)
RETURNS
SMALLDATETIME
AS
BEGIN
DECLARE @Result SMALLDATETIME
DECLARE @StartDate SMALLDATETIME
DECLARE @DayOfWeek INT

-- Get Starting date, which is first day of the month
SET @StartDate = CONVERT(SmallDateTime,CAST(@Month AS VARCHAR(2))+'/1/'+CAST(@Year AS VARCHAR(4)))
SET @DayOfWeek = DatePart(dw,@StartDate) -- Falls on what day?
IF @DayOfWeek < @weekDay
SET @StartDate = DateAdd(d,@weekDay-@DayOfWeek,@StartDate) -- Adjust to requested day of week
IF @DayOfWeek > @weekDay
SET @StartDate = DateAdd(d,@DayOfWeek-@weekDay,@StartDate) -- Adjust to requested day of week
SET @Result = DateAdd(wk,@Occur-1,@StartDate) -- Get X occurrence of day

RETURN @Result
END

GO
---------------------------------------------------------------------------------
-- Returns a virtual table containing all holidays for a given year
---------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[Holiday_List] (@nYear INT)
RETURNS @Holidays TABLE
(Holiday_name VARCHAR(32),
Holiday_date SMALLDATETIME
)
AS
BEGIN
-- Calculate Easter Sunday
DECLARE @g INT
DECLARE @c INT
DECLARE @h INT
DECLARE @i INT
DECLARE @j INT
DECLARE @l INT
DECLARE @Month INT
DECLARE @Day INT
DECLARE @Easter SMALLDATETIME
DECLARE @WorkDT SMALLDATETIME

-- Bizarre Algorithm to determine Easter Sunday
SET @g = @nYear % 19
SET @c = @nYear / 100
SET @h = ((@c - (@c / 4) - ((8 * @c + 13) / 25) + (19 * @g) + 15) % 30)
SET @i = @h - ((@h / 28) * (1 - (@h /28) * (29 / (@h + 1)) * ((21 - @g) / 11)))
SET @j = ((@nYear + (@nYear / 4) + @i + 2 - @c + (@c / 4)) % 7)
SET @l = @i - @j
SET @Month = 3 + ((@l + 40) / 44)
SET @Day = @l + 28 - (31 * (@Month / 4))
SET @Easter = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@nYear AS VARCHAR(4))

------------------------------------------------------------------------------------------------
-- Add Easter Sunday to holiday list, and get holidays based around Easter
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Easter',@Easter)
-- Good Friday
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Good Friday',DateAdd(d,-2,@Easter))
-- Palm Sunday
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Palm Sunday',DateAdd(ww,-1,@Easter))
-------------------------------------------------------------------------------------------------

-- Fixed date holidays are loaded next
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('New Year''s Day',CONVERT(SmallDateTime,'1/1/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Groundhog Day',CONVERT(SmallDateTime,'2/2/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Lincoln''s Birthday',CONVERT(SmallDateTime,'2/12/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Valentines Day',CONVERT(SmallDateTime,'2/14/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('St. Patrick''s Day',CONVERT(SmallDateTime,'3/17/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('April Fools Day',CONVERT(SmallDateTime,'4/1/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Flag Day',CONVERT(SmallDateTime,'6/14/'+CAST(@nYear AS VARCHAR(4))))
IF @nYear >=1776 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Independence Day',CONVERT(SmallDateTime,'7/4/'+CAST(@nYear AS VARCHAR(4))))
IF @nYear >=1958 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Boss''s Day',CONVERT(SmallDateTime,'10/16/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Halloween',CONVERT(SmallDateTime,'10/31/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Christmas',CONVERT(SmallDateTime,'12/25/'+CAST(@nYear AS VARCHAR(4))))
IF @nYear >=1966 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Kwanzaa',CONVERT(SmallDateTime,'12/26/'+CAST(@nYear AS VARCHAR(4))))

-- Holidays that full on the same day of the week (based on the year they were officially established)
IF @nYear>=1983 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Martin Luther King Day',dbo.FloatingDate(3,2,1,@nYear)) -- 3rd Monday in January
IF @nYear>=1993 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Take your Daughter to Work Day',dbo.FloatingDate(4,5,4,@nYear)) -- 4th Thursday in April
IF @nYear>=1908 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Mothers Day',dbo.FloatingDate(2,1,5,@nYear)) -- 2nd Sunday in May
IF @nYear>=1950 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Armed Forces Day',dbo.FloatingDate(3,7,5,@nYear)) -- 3rd Saturday in May
IF @nYear>=1910 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Fathers Day',dbo.FloatingDate(2,1,6,@nYear)) -- 2nd Sundy in June
IF @nYear>=1894 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Labor Day',dbo.FloatingDate(1,2,9,@nYear)) -- 1st Monday in September
IF @nYear>=1941 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Thanksgiving',dbo.FloatingDate(4,5,11,@nYear)) -- 4th Thursday in November

---------------------------------------------------------------------------------------
-- Federal holidays that only come every 4 years
---------------------------------------------------------------------------------------
IF (@nYear >= 1937) AND ((@nYear-1937) % 4)=0
BEGIN
SET @WorkDT = CONVERT(SmallDateTime,'01/20/'+CAST(@nYear AS VARCHAR(4))) -- Get Inauguration day
IF DatePart(dw,@WorkDT)=1 SET @WorkDt = DateAdd(d,1,@WorkDt) -- Move to Monday if it falls on Sunday
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Inauguration Day',@WorkDt)
END

IF (@nYear < 1937) AND ((@nYear-1937) % 4)=0
BEGIN
-- Get Inauguration day for years prior to 1937
SET @WorkDT = CONVERT(SmallDateTime,'03/04/'+CAST(@nYear AS VARCHAR(4)))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Inauguration Day',@WorkDt)
END
---------------------------------------------------------------------------------------
-- Holidays that change based upon year
---------------------------------------------------------------------------------------
IF @nYear >= 1971
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Presidents Day',dbo.FloatingDate(3,2,2,@nYear)) -- 3rd Monday in February
IF @nYear < 1971
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Washington''s Birthday',CONVERT(SmallDateTime,'2/22/'+CAST(@nYear AS VARCHAR(4))))

IF (@nYear >=1954)
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Veteran''s Day',CONVERT(SmallDateTime,'11/11/'+CAST(@nYear AS VARCHAR(4))))
IF (@nYear >=1921 and @nYear < 1954)
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Armistice',CONVERT(SmallDateTime,'11/11/'+CAST(@nYear AS VARCHAR(4))))

IF @nYear>=1971
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Columbus Day',dbo.FloatingDate(2,2,10,@nYear)) -- 2nd Monday in October
IF @nYear>=1937 and @nYear <1971
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Columbus Day',CONVERT(SmallDateTime,'10/12/'+CAST(@nYear AS VARCHAR(4))))


RETURN
END

GO

SELECT * FROM dbo.Holiday_List(2005)
ORDER BY 2





kwhite
kwhite
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 23
The floating date function does not work correctly for 2005 (I didn't check other years)
if you modify the FloatingDate function as follows it will work:
Thanks to Karl Schmitt -Deerfield Bakery http://www.tek-tips.com/faqs.cfm?fid=5075
who also has a calendar generator

ALTER FUNCTION dbo.FloatingDate(@Occur INT,@WeekDay INT,@Month INT,@Year INT)
RETURNS
SMALLDATETIME
AS
BEGIN
DECLARE @Result SMALLDATETIME
DECLARE @StartDate SMALLDATETIME


-- Get Starting date, which is first day of the month
SET @StartDate = CONVERT(SmallDateTime,CAST(@Month AS VARCHAR(2))+'/1/'+CAST(@Year AS VARCHAR(4)))

SET @Result = cast(str(@Month)+'/'+ str((7+ @Weekday-datepart(dw,@StartDate))%7+1) +'/'+
str(@Year) AS datetime)+(@Occur-1)*7

RETURN @Result
END

GO



May you live to be 100 and me 100 but minus a day so I never know that nice people like you have passed away
Paul Cresham
Paul Cresham
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 44
In case it's of use, I used this script to build the data dimension in a data warehouse. Bank Holidays are based on UK holidays though. The UDFs dbo.WorkingDay and the functions it uses for Easter & holiday dates are elsewhere on the site (I've posted them before) so you can find them by using Search or looking at my posting history.

IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] = 'DX_Date' AND [type] = 'U')
DROP TABLE dbo.DX_Date
GO

CREATE TABLE dbo.DX_Date
( DateKey int IDENTITY(1,1) NOT NULL,
SQLDate smalldatetime NOT NULL,
DayOfWeek tinyint NOT NULL,
DayOfWeekName varchar(9) NOT NULL,
DayOfMonth tinyint NOT NULL,
DayOfYear smallint NOT NULL,
LastDayOfWeek_B bit NOT NULL,
LastDayOfMonth_B bit NOT NULL,
WeekEndingDate char(8) NOT NULL,
WeekNumber tinyint NOT NULL,
MonthName varchar(9) NOT NULL,
MonthNumber tinyint NOT NULL,
YearMonth char(6) NOT NULL,
Quarter tinyint NOT NULL,
YearQuarter char(6) NOT NULL,
Year smallint NOT NULL,
FinDayOfYear smallint NOT NULL,
FinWeekNumber tinyint NOT NULL,
FinPeriod tinyint NOT NULL,
FinYearPeriod char(6) NOT NULL,
FinQuarter tinyint NOT NULL,
FinYearQuarter char(6) NOT NULL,
FinYear smallint NOT NULL,
BankHoliday_B bit NOT NULL,
Weekday_B bit NOT NULL,
CONSTRAINT DX_Date_PK PRIMARY KEY (DateKey)
)
GO

-- Populate table

CREATE TABLE #numbers
( n int IDENTITY(1,1) PRIMARY KEY CLUSTERED )

WHILE ISNULL(SCOPE_IDENTITY(),0) <= 100000
BEGIN
INSERT #numbers DEFAULT VALUES
END
GO

IF @@DATEFIRST != 7
PRINT 'WARNING: @@DATEFIRST != 7'

DECLARE @dtStartDate datetime, @dtEndDate datetime, @iDays int

SET @dtStartDate = CONVERT(datetime, '19991231')
SET @dtEndDate = CONVERT(datetime, '20101231')

SET @iDays = DATEDIFF(dd, @dtStartDate, @dtEndDate)

INSERT DX_Date (SQLDate, DayOfWeek, DayOfWeekName, DayOfMonth, DayOfYear, LastDayOfWeek_B, LastDayOfMonth_B, WeekEndingDate,
WeekNumber, MonthName, MonthNumber, YearMonth, Quarter, YearQuarter, Year, FinDayOfYear, FinWeekNumber, FinPeriod,
FinYearPeriod, FinQuarter, FinYearQuarter, FinYear, BankHoliday_B, Weekday_B)

SELECT DATEADD(dd, n, @dtStartDate),
DATEPART(dw, DATEADD(dd, n, @dtStartDate)),
DATENAME(dw, DATEADD(dd, n, @dtStartDate)),
DATEPART(dd, DATEADD(dd, n, @dtStartDate)),
DATEPART(dy, DATEADD(dd, n, @dtStartDate)),
CASE WHEN DATEPART(dw, DATEADD(dd, n, @dtStartDate)) = 1 THEN 1 ELSE 0 END,
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) = DATEPART(mm, DATEADD(dd, n + 1, @dtStartDate)) THEN 0 ELSE 1 END,
CONVERT(varchar, DATEADD(dd, n + ((8 - DATEPART(dw, DATEADD(dd, n, @dtStartDate))) % 7), @dtStartDate), 112),
DATEPART(wk, DATEADD(dd, n, @dtStartDate)),
DATENAME(mm, DATEADD(dd, n, @dtStartDate)),
DATEPART(mm, DATEADD(dd, n, @dtStartDate)),
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate))) +
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) < 10
THEN '0' + CONVERT(varchar, DATEPART(mm, DATEADD(dd, n, @dtStartDate)))
ELSE CONVERT(varchar, DATEPART(mm, DATEADD(dd, n, @dtStartDate))) END,
DATEPART(qq, DATEADD(dd, n, @dtStartDate)),
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate))) + '0' + CONVERT(varchar, DATEPART(qq, DATEADD(dd, n, @dtStartDate))),
DATEPART(yy, DATEADD(dd, n, @dtStartDate)),
DATEDIFF(dd, CONVERT(datetime,
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate)) -
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) <= 4 THEN 1 ELSE 0 END) + '0501'),
DATEADD(dd, n, @dtStartDate)) + 1,
DATEDIFF(ww, CONVERT(datetime,
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate)) -
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) <= 4 THEN 1 ELSE 0 END) + '0501'),
DATEADD(dd, n, @dtStartDate)) + 1,
(DATEPART(mm, DATEADD(dd, n, @dtStartDate)) + 8) % 12,
'XXXXXX',
0,
'XXXXXX',
DATEPART(yy, DATEADD(dd, n, @dtStartDate)) -
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) <= 4 THEN 1 ELSE 0 END,
CASE WHEN eBIS_INTERACTIVE.dbo.WorkingDay(DATEADD(dd, n, @dtStartDate)) = 2 THEN 0 ELSE 1 END,
CASE WHEN eBIS_INTERACTIVE.dbo.WorkingDay(DATEADD(dd, n, @dtStartDate)) = 1 THEN 0 ELSE 1 END
FROM #numbers
WHERE n <= @iDays
GO

UPDATE DX_Date SET FinPeriod = 12 WHERE FinPeriod = 0
GO

UPDATE DX_Date
SET FinYearPeriod = CONVERT(varchar, FinYear) + CASE WHEN FinPeriod < 10 THEN '0' ELSE '' END + CONVERT(varchar, FinPeriod),
FinQuarter = CASE WHEN FinPeriod IN (1,2,3) THEN 1
WHEN FinPeriod IN (4,5,6) THEN 2
WHEN FinPeriod IN (7,8,9) THEN 3
WHEN FinPeriod IN (10,11,12) THEN 4 END
GO

UPDATE DX_Date
SET FinYearQuarter = CONVERT(varchar, FinYear) + '0' + CONVERT(varchar, FinQuarter)
GO

UPDATE DX_Date
SET FinWeekNumber = 1
WHERE FinWeekNumber = 53
GO

-- Create index

CREATE NONCLUSTERED INDEX DX_Date_IX1
ON DX_Date (SQLDate)
WITH FILLFACTOR = 100
GO

DROP TABLE #numbers
GO
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search