Date Dimension Generator

Following SQL query is to create simple date dimension table and then insert values to the given range.

Create table

CREATE TABLE [dbo].[DimDate](
[Date] [date] NOT NULL,
[Day] [varchar](10) NULL,
[Month] [varchar](10) NULL,
[Year] [int] NULL
) ON [PRIMARY]
GO

Script to generate date


  DECLARE @startDate date;
  DECLARE @endDate date;

  SET @startDate = '2017-01-01';
  SET @endDate = '2018-12-31';

 
  WHILE(@startDate <= @endDate)
            BEGIN
                        INSERT dbo.DimDate
                        SELECT
CONVERT(Datetime, @startDate, 120) [Date],
DATENAME(DW,@startDate) [Day],
DATENAME(MM,@startDate) [Month],
DATEPART(YY,@startDate) [Year]

                        SET @startDate = DATEADD(DD, 1, @startDate )
            END