Date Dimension

Below is the script to create Date Dimension.

USE [DatabaseName]
GO

IF OBJECT_ID('Date','U') IS NOT NULL
DROP TABLE Date
GO


CREATE TABLE [dbo].[Date](
   [DateSK] [int] NOT NULL,
   [FullDate] [datetime] NOT NULL,
   [DateName] [char](11) NOT NULL,
   [DayOfWeek] [tinyint] NOT NULL,
   [DayNameOfWeek] [char](10) NOT NULL,
   [DayOfMonth] [tinyint] NOT NULL,
   [DayOfYear] [smallint] NOT NULL,
   [WeekdayWeekend] [char](7) NOT NULL,
   [WeekOfYear] [tinyint] NOT NULL,
   [MonthName] [char](10) NOT NULL,
   [MonthOfYear] [tinyint] NOT NULL,
   [CalendarQuarter] [tinyint] NOT NULL,
   [CalendarYear] [smallint] NOT NULL,
   [CalendarYearMonth] [char](7) NOT NULL,
   [CalendarYearQtr] [char](15) NOT NULL,
CONSTRAINT PK_Date_DateID PRIMARY KEY (DateSK)
) ON [PRIMARY]
GO
RAISERROR('Table Date created successfully!',0,1)

DECLARE @StartDate datetime, @EndDate datetime
-- Set StartDate and EndDate as per your requirement
SELECT @StartDate = '2009-01-01', @EndDate = '2010-12-31'

WHILE (@StartDate <= @EndDate )
BEGIN
INSERT INTO Date
SELECT
   CAST(CONVERT(varchar(8),@StartDate,112) AS int) DateSK
   ,@StartDate AS [Date]
   ,CONVERT(varchar(20),@StartDate,106) AS DateName
   ,DATEPART(DW,@StartDate)   [DayOfWeek]
   ,DATENAME(DW,@StartDate) [DayNameOfWeek]
   ,DATENAME(DD,@StartDate)  [DayOfMonth]
   ,DATENAME(DY,@StartDate)  [DayOfYear]
   ,CASE WHEN DATEPART(DW,@StartDate) IN (1,7) THEN 'WeekEnd'
             ELSE 'WeekDay' END     [WeekdayWeekend]
   ,DATEPART(WW,@StartDate) [WeekOfYear]
   ,DATENAME(MM ,@StartDate) [MonthName]
   ,DATEPART(MM ,@StartDate)   [MonthOfYear]
   ,DATEPART(QQ,@StartDate)     [CalendarQuarter]
   ,DATEPART(YY ,@StartDate)    [CalendarYear]
   ,DATENAME(YY,@StartDate)+'-'+RIGHT('0'+CAST(Month(@StartDate) as varchar),2)  [CalendarYearMonth]
   ,DATENAME(YY,@StartDate)+'-Q'+DATENAME(QQ,@StartDate) [CalendarYearQtr]

   SET @StartDate = @StartDate +1
END
GO


Date Dimension is ready to use as soon as you execute this script in required database.
You can create following Calendar Hierarchy in your dimension to provide drill down featrues for Growth, Trends, and ToDate calculations (e.g. YTD, QTD, MTD).
Year-->Quarter-->Month-->Week-->Date