DimDate Dimension

If you are looking for a DimDate script for your EDW, this one may work for you.


IF OBJECT_ID('DimDate') IS NOT NULL 
    DROP TABLE DimDate
GO


CREATE TABLE [dbo].[DimDate]
    (
      [DateKey] [int] NOT NULL ,
      [CalendarDate] [smalldatetime] NULL ,
      [CalendarWeek] [varchar](8) NULL ,
      CalendarWeekNumber INT ,
      [CalendarMonth] [varchar](8) NULL ,
      [CalendarMonthName] [varchar](30) NULL ,
      CalendarMonthNumber INT ,
      [CalendarQuarter] [varchar](8) NULL ,
      [CalendarYear] [varchar](4) NULL ,
      [MonthStart] [smalldatetime] NULL ,
      [MonthEnd] [smalldatetime] NULL ,
      WeekDay INT ,
      WeekDayName VARCHAR(20) ,
      WeekDayType VARCHAR(20)
        CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED ( [DateKey] ASC )
    )
ON  [PRIMARY]
GO
CREATE UNIQUE INDEX IX_DimDate ON DimDate(CalendarDate)
GO


--======================================
-- Populate the dimension
--======================================
;
WITH    DateCTE
          AS ( SELECT   CAST('1995-01-01' AS DATE) AS DateValue
               UNION ALL
               SELECT   DATEADD(d, 1, DateValue)
               FROM     DateCTE
               WHERE    DATEADD(d, 1, DateValue) < '2015-01-01'
             )
    INSERT  INTO DimDate
            SELECT  CAST(CONVERT(CHAR(8), CAST(DateValue AS DATETIME), 112) AS INT) AS DateKey ,
                    CAST(DateValue AS SMALLDATETIME) AS CalendarDate ,
                    CAST(YEAR(DateValue) AS CHAR(4)) + '-'
                    + CAST(DATEPART(wk, DateValue) AS VARCHAR(2)) AS CalendarWeek ,
                    DATEPART(wk, DateValue) AS CalendarWeekNumber ,
                    CAST(YEAR(DateValue) AS CHAR(4)) + '-'
                    + CASE WHEN DATEPART(m, DateValue) < 10
                           THEN '0' + CAST(DATEPART(m, DateValue) AS CHAR(4))
                           ELSE CAST(DATEPART(m, DateValue) AS CHAR(4))
                      END AS CalendarMonth ,
                    DATENAME(MONTH, DateValue) AS CalendarMonthName ,
                    DATEPART(m, DateValue) AS CalendarMonthNumber ,
                    CAST(YEAR(DateValue) AS CHAR(4))
                    + CAST(DATEPART(Quarter, DateValue) AS CHAR(1)) AS CalendarQuarter ,
                    YEAR(DateValue) AS CalendarYear ,
                    CAST(CONVERT(VARCHAR(10), ( CONVERT(CHAR(6), CAST(DateValue AS DATETIME), 112)
                                                + '01' ), 120) AS SMALLDATETIME) AS CalendarMonthStart ,
                    CAST(CONVERT(VARCHAR(10), CAST(CONVERT(CHAR(8), CAST(CONVERT(VARCHAR(12), DATEADD(day,
                                                              -1
                                                              * DAY(DATEADD(month,
                                                              1, DateValue)),
                                                              DATEADD(month, 1,
                                                              DateValue)), 113) AS DATETIME), 112) AS INT), 120) AS SMALLDATETIME) AS CalendarMonthEnd ,
                    DATEPART(dw, DateValue) AS WeekDay ,
                    DATENAME(dw, DateValue) AS WeekDayName ,
                    CASE WHEN DATENAME(dw, DateValue) IN ( 'Saturday',
                                                           'Sunday' )
                         THEN 'WeekEnd'
                         ELSE 'WeekDay'
                    END AS WeekDayType
            FROM    DateCTE a
            ORDER BY DateKey
    OPTION  ( MAXRECURSION 32000 )
GO


SELECT * FROM  dbo.DimDate