Holiday Calendar Generator

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

     

  • 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

  • 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-2         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-2         = ((@nYear + (@nYear / 4) + @i + 2 - @C + (@c / 4)) % 7)

        SET @l        = @i - @j-2

        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

  • 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

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

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

    SETFinYearQuarter = CONVERT(varchar, FinYear) + '0' + CONVERT(varchar, FinQuarter)

    GO

    UPDATE DX_Date

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply