Simple Steps to create Date Dimension in Microsoft SQL Server.
CREATE TABLE Numbers_Small (Number INT);
INSERT INTO Numbers_Small VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-- Works in SQL Server 2008.Insert statements has to be modified for older versions.
CREATE TABLE Numbers_Big (Number_Big BIGINT);
INSERT INTO Numbers_Big ( Number_Big )
SELECT thousands.number * 1000 + hundreds.number * 100 + tens.number * 10 + ones.number as number_big
FROM numbers_small thousands, numbers_small hundreds, numbers_small tens, numbers_small ones;
CREATE TABLE [dbo].[Date_D](
[DateKey] [int] NOT NULL,
[Date] [datetime] NOT NULL,
[Day] [char](10) NULL,
[DayOfWeek] [smallint] NULL,
[DayOfMonth] [smallint] NULL,
[DayOfYear] [smallint] NULL,
[PreviousDay] [datetime] NULL,
[NextDay] [datetime] NULL,
[WeekOfYear] [smallint] NULL,
[Month] [char](10) NULL,
[MonthOfYear] [smallint] NULL,
[QuarterOfYear] [smallint] NULL,
[Year] [int] NULL
);
INSERT INTO Date_D (DateKey, Date)
SELECT number_big, DATEADD(day, number_big, '2010-01-01') as Date
FROM numbers_big
WHERE DATEADD(day, number_big, '2010-01-01') BETWEEN '2010-01-01' AND '2010-12-31'
ORDER BY number_big;
Use the following INSERT statement, if you want use a date format(eg: 20100203) as key instead of a number(eg: 123)
INSERT INTO Date_D (DateKey, Date)
SELECT CONVERT(INT, CONVERT(CHAR(10),DATEADD(day, number_big, '2010-01-01'), 112)) as DateKey,
CONVERT(DATE,DATEADD(day, number_big, '2010-01-01')) as Date
FROM numbers_big
WHERE DATEADD(day, number_big, '2010-01-01') BETWEEN '2010-01-01' AND '2010-12-31'
ORDER BY 1;
In above step, the date dimension records will be created for year 2010. Change the dates in the above statement to change the range.
UPDATE Date_D
SET Day = DATENAME(DW, Date),
DayOfWeek = DATEPART(WEEKDAY, Date),
DayOfMonth = DAY(Date),
DayOfYear = DATEPART(DY,Date),
PreviousDay = DATEADD(DAY, -1, Date),
NextDay = DATEADD(DAY, 1, Date),
WeekOfYear = DATEPART(WK,Date),
Month = DATENAME(MONTH,Date),
MonthOfYear = MONTH(Date),
QuarterOfYear = DATEPART(Q, Date),
Year = YEAR(Date);
DROP TABLE Numbers_Small;
DROP TABLE Numbers_Big;