Creating a Simple Date Dimension Using Recursive Common Table Expressions (CTE)

In this post I’ll explain how to create a simple date dimension to use it in your data warehouses and your BI solutions. So, this article is for you if you need a fast and easy way to make a simple date dimension that supports the most commonly used date elements like

·         Integer date key

·         Different date formats

·         Quarter

·         Month names

·         Week numbers

·         Day of the week

·         Day of the year

·         Is day end of month

·         Not available (N/A) row

Due to the fact that there are lots of fellows that are still using SQL Server 2008 and earlier, I put the codes that support SQL Server 2008  as well as SQL Server 2012. But, I’ve commented the 2008 lines.

I, myself, was looking for a simple date dimension and I’ve found a bunch of them over the Internet. But, what I don’t like about most of them is that those solutions are inserting data on a row-by-row basis using a while loop that seems to be a bit slow at the first time of populating the table. That was the first reason that I decided to re-invent the wheel and make something that is simple and easy to implement and at the same time is running fast.

Now, let’s talk about the solution. The following code generates a date dimension, I called it DimDate, using recursive Common Table Expressions (CTE).

First of all create a DimDate.

 

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N’DimDate’))

CREATE TABLE [dbo].[DimDate](

       [DateAlternateKey] [date] NULL,

       [UKDateFormat] [varchar](10) NULL,

       [USDateFormat] [varchar](10) NULL,

       [GermanDateFormat] [varchar](10) NULL,

       [FullDate] [varchar](11) NULL,

       [DateKey] [int] NOT NULL PRIMARY KEY,

       [Year] [int] NULL,

       [Quarter] [int] NULL,

       [QuarterDescription] [varchar](6) NOT NULL,

       [Month] [varchar](9) NULL,

       [MonthNumber] [int] NULL,

       [MonthYear] [varchar](7) NULL,

       [WeekNumber] [int] NULL,

       [WeekNumberDescription] [varchar](5) NOT NULL,

       [DayOfWeek] [varchar](10) NULL,

       [DayOfMonth] [int] NULL,

       [DayOfYear] [int] NULL,

       [EndOfMonth] [int] NULL,

       [IsDayEndOfMonth] [varchar](3) NOT NULL

) ON [PRIMARY]

 

GO

 

Notes:

·         If for any reason, you do NOT want to create the DimDate first and then populate it, just uncomment the “–into DimDate” line from the code.

·         If you decided to create the DimDate table first, then uncomment the –INSERT INTO [dbo].[DimDate] line.

·         The starting date is set to ‘1990-01-01’ and the end date is defined to be ‘2030-12-31’.

·         As the default maximum number of recursions in a CTE is 100, the option (maxrecursion 0) should be added to the code.

·         For supporting Not Available (N/A) dates, if you are implementing an SSAS Tabular Model then use ‘1900-01-01’ in union all, otherwise you can use a NULL instead.

·         You can add public holidays of your own country to the solution, but, I didn’t do that to keep the solution simple.

·         You can also calculate fiscal (financial) dates, but, again I didn’t do that to keep it simple. (However, I might add fiscal dates to the solution in the future. Who knows?!)

 

;WITH DimDateCTE AS

(SELECT CAST(‘1990-01-01’ AS DATE) DATE_

 UNION ALL

 SELECT DATEADD(DAY,1,DimDateCTE.DATE_) DATE_ FROM DimDateCTE WHERE DATE_ <‘2030-12-31’

)

–INSERT INTO [dbo].[DimDate] –Use this part if there is an existing DimDate

SELECT DATE_ DateAlternateKey

       , CONVERT(VARCHAR(10), DATE_, 103) UKDateFormat — British/French Date Format

       , CONVERT(VARCHAR(10), DATE_, 101) USDateFormat

       , CONVERT(VARCHAR(10), DATE_, 104) GermanDateFormat

       , CONVERT(VARCHAR(11), DATE_, 106) FullDate

       , CAST(CONVERT(VARCHAR(8),DATE_, 112) as int) DateKey

       , YEAR(DATE_) Year

       , DATENAME(qq, DATE_) Quarter

       –, ‘Qtr ‘+ CAST(DATENAME(qq, DATE_) as VARCHAR(1)) QuarterDescription — For SQL Server 2008 and earlier

       , CONCAT(‘Qtr ‘, DATENAME(qq, DATE_)) QuarterDescription  –Supported in 2012 and above

       , DATENAME(M,DATE_) Month

       , MONTH(DATE_) MonthNumber

       , RIGHT(CONVERT(VARCHAR(10), DATE_, 103), 7) MonthYear

       , DATENAME(wk,DATE_) WeekNumber

       –, ‘Wk ‘+CAST(DATENAME(wk,DATE_) as VARCHAR(2)) WeekNumberDescription — For SQL Server 2008 and earlier

       , CONCAT(‘Wk ‘, DATENAME(wk,DATE_)) WeekNumberDescription  –Supported in 2012 and above

       , DATENAME(dw,DATE_) DayOfWeek

       , DATEPART(dd,DATE_) DayOfMonth

       , DATENAME(dy,DATE_) DayOfYear

       –, DATEPART(dd,DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATE_)+1,0))) EndOfMonth — For SQL Server 2008 and earlier

       –, CASE WHEN DATEPART(dd,DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATE_)+1,0)))= DATEPART(dd,DATE_) THEN ‘Yes’ ELSE ‘No’ END IsDayEndOfMonth — For SQL Server 2008 and earlier

       , DATEPART(dd,EOMONTH(DATE_)) EndOfMonth –Supported in 2012 and above

       , IIF(DATEPART(dd,EOMONTH(date_))= DATEPART(dd,DATE_), ‘Yes’,‘No’) IsDayEndOfMonth –Supported in 2012 and above

–INTO DimDate –Use this line to create a new DimDate table

FROM DimDateCTE

UNION ALL –Supporting N/A dates

SELECT ‘1900-01-01’ –This should be 1900-01-01 for SSAS Tabular Model. Otherwise, a NULL can be used instead.

       , ‘N/A’

       , ‘N/A’

       , ‘N/A’

       , ‘N/A’

       , –1

       , –1

       , –1

       , ‘N/A’

       , ‘N/A’

       , –1

       , ‘N/A’

       , –1

       , ‘N/A’

       , ‘N/A’

       , –1

       , –1

       , –1

       , ‘N/A’

OPTION (MAXRECURSION 0)

 

The results should be like this:

 

Enjoy!