Date Dimension SQL Scripts – MS SQL Server

Post Info: 3,272 views 1 Comments Post a comment

Simple Steps to create Date Dimension in Microsoft SQL Server.

  • 1. Create Two numbers table for sequence/key generation.

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;

  • 2. Create the Date Dimension table.

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
);

  • 3. First create DateKey and Date fields.

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.

  • 4. Update all other fields with appropriate data.

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);

  • 5. Drop the temporary number tables.

DROP TABLE Numbers_Small;
DROP TABLE Numbers_Big;

Return to top

1 Comment

  • Tech Sawi says on May 22, 2012 at 6:08 am | Permalink

    Fantastic, superb. Was looking for it for some time now. Thanks.

    Another ques, is it possible to have Time-based dimensions? We have requirement wherein user wants to pull data based on specific times. Using CONVERT is taking too much time. appreciate if you could help.

Leave a Reply

 

Are you finished?

Return to top