TSQL Date Dimension Data Population Script

This post on DateDimension Data Population script. Back to TSQL Days...........

	--Table Creation for Date Dimension Upto Minute level
	IF OBJECT_ID ('DimDateTime') IS NOT NULL DROP TABLE DimDateTime
	GO
	CREATE TABLE [dbo].[DimDateTime](
	[DATEKEY] [bigint] NOT NULL,
	[DateATime] [datetime] NOT NULL,
	[HourOfDay] [int] NOT NULL,
	[MinuteOfDay] [int] NOT NULL,
	[DayofWeekName] [int] NOT NULL,
	[WeekInt] [int] NOT NULL,
	[MonthInt] [int] NOT NULL,
	[QuarterInt] [int] NOT NULL,
	[YearInt] [int] NOT NULL
	CONSTRAINT [pk_DimDateTime] PRIMARY KEY CLUSTERED
	(
	[DATEKEY] ASC
	))
	--Date Dimension Data Population Script
	DECLARE @StartDate DATETIME, @EndDate DATETIME
	SET @StartDate = '2014-01-01'
	SET @EndDate = '2018-01-01'
	WHILE (@StartDate < @EndDate)
	BEGIN
	INSERT INTO [dbo].[DimDateTime]
	([DATEKEY],[DateATime],[MinuteOfDay],[HourOfDay],[DayofWeekName],[WeekInt],[MonthInt],[QuarterInt],[YearInt])
	select convert(bigint,LEFT(CONVERT(varchar(20),@StartDate,112) + REPLACE(CONVERT(varchar(5),@StartDate,108),':',''),12)),
	@StartDate,
	DATEPART(minute,@StartDate),
	DATEPART(hour,@StartDate),
	DATEPART(dw,@StartDate),
	DATEPART(wk,@StartDate),
	DATEPART(mm,@StartDate),
	DATEPART(qq,@StartDate),
	YEAR(@StartDate)
	SET @StartDate = DATEADD(minute,1,@StartDate)
	END

view raw DateDimension.sql hosted with ❤ by GitHub

Happy Learning!!!