SQL Script - Time Dimension

DROP TABLE Dim_Time_Generated
CREATE TABLE Dim_Time_Generated
(
    DateID       VARCHAR(8) NOT NULL    PRIMARY KEY
,   MonthName    VARCHAR(9) NOT NULL
,   MonthNumber  INT        NOT NULL
,   DayNumber    INT        NOT NULL
,   DayName      VARCHAR(9) NOT NULL
,   Quarter      INT        NOT NULL
,   QuarterName  CHAR(7)    NOT NULL
,   YEAR         INT        NOT NULL
,   FullDate DATETIME NOT NULL
)

BEGIN
    SET DATEFIRST 1
    DECLARE @startdate  DATETIME
    DECLARE @enddate    DATETIME
    DECLARE @DATE       DATETIME
    DECLARE @offset     INT
    DECLARE @dateId     VARCHAR(8)
    SET     @startdate  = '2009-01-01'
    SET     @enddate    = '2011-12-31'
    SET     @offset     = 0
    SET     @DATE       = DATEADD(dd, @offset, @startdate)
    WHILE @DATE <= @enddate
    BEGIN
        SET @dateId = CONVERT(VARCHAR(8), @DATE, 112)   -- Convert to yyyymmdd format
        INSERT INTO Dim_Time_Generated(DateID,MonthName,MonthNumber,DayNumber,DayName,Quarter,QuarterName,YEAR,FullDate)
        VALUES ( @dateId             --DateID
            ,DATENAME(mm, @DATE)     --MonthName
            ,DATEPART(mm, @DATE)     --MonthNumber
            ,DATEPART(dd, @DATE)     --DayOfMonth
            ,DATENAME(DW, @DATE)     --DayNAme
            ,DATEPART(qq, @DATE)     --Quarter
            ,'Q' + DATENAME(qq, @DATE)   --QuarterName
            ,DATEPART(yy, @DATE)     --Year
            ,@DATE                   --FullDate
        )
        SET  @offset    = @offset + 1
        SET  @DATE      = DATEADD(dd, @offset, @startdate)
    END
END
GO