DimDate - Date Dimension

The below script helps to create valid date dimension attribures:


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