How to create a Time Table

Key points: 

• Time is a dimension that never has to miss in a Data Warehouse. 
• This script generate a table with a daily granularity.
• The key of the table must have the int format yyyymmdd.

Script:

-- Create dimDate table
CREATE TABLE [DWH].[DimDate]
     (
        DateId  INT NOT NULL,
Date DATETIME NOT NULL,
        Year VARCHAR(4) NULL,
        Quarter VARCHAR(20) NULL,
        Month VARCHAR(2) NULL,
        MonthName VARCHAR(20) NULL,
Day VARCHAR(2) NULL
        CONSTRAINT PK_Date PRIMARY KEY CLUSTERED (DateId ASC)
        ON [PRIMARY]
     )
GO

-- Creation of variables to set start and end period
DECLARE @StartDate datetime='01/01/2011'
DECLARE @EndDate datetime='01/01/2020'

--Loop to insert date in table
DECLARE @DateInProcess datetime
SET @DateInProcess=@StartDate

WHILE @DateInProcess<=@EndDate
BEGIN
-- Add a record in table for this date
INSERT INTO [DWH].[DimDate] 
(
[DateId]
, [Date]
, [Year]
, [Quarter]
, [Month]
, [MonthName]
, [Day]
)
VALUES (
-- DateId
   CAST(CONVERT(VARCHAR(8),@DateInProcess, 112) AS INT)
-- Date
,   @DateInProcess
-- Year
, Year ( @DateInProcess )
-- Quarter
, DateName ( quarter, @DateInProcess )
-- Month
, Month ( @DateInProcess )
-- MonthName
, Cast( Year( @DateInProcess ) as varchar(4) )+' - '
+ DateName( month, @DateInProcess )
-- Day
, Day ( @DateInProcess)
)

-- Add day to execute a new loop
SET @DateInProcess=DATEADD(d, 1, @DateInProcess)
END