Populate Date dimension table

Date dimension plays very important role in most of the BI solutions and if someone wants to create a Date dimension then first requirement is "how to populate the required Date dimension table". So I thought let's share a SQL script which I have created for populating Date dimension table.
If you execute the following SQL script, then that will create and populate the Date dimension table with the same data as used in Adventureworks sample database.


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDate]') AND type in (N'U'))
DROP TABLE [dbo].[DimDate]

GO

WITH DateDimension_CTE as
(
SELECT Cast ('2008-01-01' as DateTime) FullDate
UNION ALL
SELECT FullDate + 1
FROM DateDimension_CTE
WHERE FullDate + 1 < = '2020-12-31'
)
SELECT
CAST(CONVERT(CHAR(8),CAST(FullDate AS DATETIME),112) AS INT) AS DateKey
,CAST(FullDate AS Date) AS FullDate
,DatePart (dw, FullDate) AS DayNumberOfWeek
,DateName (dw, FullDate) as EnglishDayNameOfWeek
,DAY (FullDate) as DayNumberOfMonth
,DatePart (dy, FullDate) as DayNumberOfYear
,DatePart (wk, FullDate) as WeekNumberOfYear
,DateName (mm, FullDate) AS EnglishMonthName
,MONTH (FullDate) AS MonthNumberOfYear
,DatePart ( qq, FullDate) AS CalendarQuarter
,YEAR (FullDate) AS CalendarYear
, (CASE WHEN MONTH (FullDate)>=1 AND MONTH (FullDate) <=6 THEN 1 ELSE 2 END) CalendarSemester
INTO DimDate
FROM DateDimension_CTE

OPTION (MAXRECURSION 0)

GO

After executing above SQL, you will find the "DimDate" dimension table under the specified database with all the columns populated with the required data.