creating a Date Dimension table

Frequently, I have to create a ‘Date Dimension’ table for complex date logic.

This is the first script I found on the internet.. that actually works.  Lots of other scripts required SQL 2008 or SQL 2012 notation..  most other scripts were about 20 pages long.

This is a very elegant solution.

http://mangalpardeshi.blogspot.com/2008/12/how-to-create-time-dimension-in-sql.html

[Now, if only this guy would realize that special characters in field names are COMPLETELY unacceptable.]


Following script creates a table named TimeDimension and populates it from 2000-01-01 to 2020-12-31. You can easily put your required dates.
Even though following query may look little big but believe me, it’s nothing but a simple SELECT statement with CTE.
——- TimeDimension ——-

WITH Mangal as
(
SELECT Cast (‘2000-01-01’ as DateTime) Date –Start Date
UNION ALL
SELECT Date + 1
FROM Mangal
WHERE Date + 1 < = ‘2020-12-31’ –End date
)

SELECT Row_Number() OVER (ORDER BY Date) as DateId
,Date
,YEAR (date) as Year
,DatePart ( qq, date) as Quarter
,MONTH (date) as Month_Number_Year
,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as Month_Number_Of_Quarter
,DatePart (wk, Date) as Week_Number_Of_Year
,datediff(wk,dateadd(qq,datediff(qq,0,date),0),date)+1 as Week_Number_Of_Quarter
,datediff(wk,dateadd(mm,datediff(mm,0,date),0),date)+1 as Week_Number_Of_Month
,DatePart (dy, date) as Day_Number_Of_Year
,datediff(dd,dateadd(qq,datediff(qq,0,date),0),date)+1 as Day_Number_Of_Quarter
,DAY (date) as Day_Number_Of_Month
,DatePart (dw, date) as Day_Number_Of_Week
,DateName (mm, date) as Month_Name
,LEFT ( DateName (mm, date), 3) Month_Name_Abbreviation
,DateName (dw, date) as Day_Name
,LEFT (DateName (dw, date), 3) as Day_Name_Abbreviation
,CONVERT(VarChar(10),date,112) as YYYYMMDD
,CONVERT(VarChar(10),date,111) as [YYYY/MM/DD]
,CONVERT(VarChar(11),date,100) as [mon dd yyyy]
,CONVERT(VarChar(11),date,120) as [yyyy-mm-dd]

INTO TimeDimension — Name of the Table

FROM Mangal

OPTION (MAXRECURSION 0)

—- Script Ends Here —-
Now your TimeDimension is ready. Do a simple
SELECT * FROM TimeDimension
for a check.
Hopefully you will find this script helpful. Any questions and suggestions are welcome.
– Mangal Pardeshi.

This entry was posted in SQL Server and tagged . Bookmark the permalink.