Date Dimension SQL Scripts – MS SQL Server

Post Info: 3,305 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.

Reply to Tech Sawi comment Click here to cancel reply

 

Are you finished?

Return to top