Generate a calendar in sql server Date Dimension Table

When we are creating the date dimension in data warehouse or data mart we need to store the date key column in the table.

Below is the structure of the Date Dimension table.

create table DimDate ( DimDate_ID    VARCHAR(20), DimDate_Date  date,     DimDate_Day    VARCHAR(20), DimDate_DayOfWeek VARCHAR(20),     DimDate_DayOfYear      VARCHAR(20), DimDate_WeekOfYear  VARCHAR(20), DimDate_WeekOfMonth             VARCHAR(20), DimDate_Month              VARCHAR(20), DimDate_MonthName varchar(20), DimDate_Quarter            VARCHAR(20), DimDate_Year VARCHAR(20) )

Table created successfully.

      

Below sql script is used to generate the date key

Declare @todate datetime,         @fromdate datetime set @fromdate = '1990-01-01' set @todate = '2050-12-31' ;With DateSequence( [Date] ) as (     Select @fromdate as [Date]         union all     Select dateadd(day, 1, [Date])         from DateSequence         where Date < @todate ) insert into DimDate Select     CONVERT(VARCHAR,[Date],112) as ID,     [Date] as [Date],     DATEPART(DAY,[Date]) as [Day],        DATENAME(dw, [Date]) as [DayOfWeek],     DATEPART(DAYOFYEAR,[Date]) as [DayOfYear],     DATEPART(WEEK,[Date]) as [WeekOfYear],                 DATEPART(WEEK,[Date]) +                 1 - DATEPART(WEEK,CAST(DATEPART(MONTH,[Date]) AS VARCHAR)                 + '/1/' + CAST(DATEPART(YEAR,[Date]) AS VARCHAR)) as [WeekOfMonth],     DATEPART(MONTH,[Date]) as [Month],     DATENAME(MONTH,[Date]) as [MonthName],     DATEPART(QUARTER,[Date]) as [Quarter],        DATEPART(YEAR,[Date]) as [Year] from DateSequence option (MaxRecursion 0)

Records are inserted into the table.

Read MaxRecursion : click here

Recursive CTE error: The maximum recursion 100 has beenexhausted before statement completion (MAXRECURSION Option in CTE)

https://bageshkumarbagi-msbi.blogspot.com/2019/08/recursive-cte-error-maximum-recursion.html

see the records in the table

Hope this sql script is helpful to generate the date key.