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.
[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
SELECT Date + 1
WHERE Date + 1 < = ‘2020-12-31’ –End date
SELECT Row_Number() OVER (ORDER BY Date) as DateId
,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
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.