Create an OLAP Main DateTime table

SET NOCOUNT ON

if exists (select * from sysobjects where id = object_id('dss_timeX') and sysstat & 0xf = 3)
	drop table dss_timeX
if not exists (select * from dbo.sysobjects where id = object_id('dbo.dss_timeX') and sysstat & 0xf = 3)
 BEGIN
CREATE TABLE dss_timeX (
	id_Time int IDENTITY (1, 1) NOT NULL ,
	FullDatedte smalldatetime NOT NULL ,
	DayText varchar (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	MonthTxt varchar (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	YearNum smallint NOT NULL ,
	DayOfMonthNum tinyint NOT NULL ,
	WeekOfYearNum smallint NOT NULL ,
	MonthOfYear tinyint NOT NULL ,
	QuarterTxt char (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	CONSTRAINT PK_dss_Time PRIMARY KEY  NONCLUSTERED 
	(
		id_Time
	),
	CONSTRAINT IXU_dss_Time$FullDate UNIQUE  CLUSTERED 
	(
		FullDatedte
	)
)
END


DECLARE @Today SmallDateTime
SET @Today = dbo.ToDateOnly('1/1/2002')

DECLARE @Counter int
SET @Counter = 0

WHILE @Counter < 6574 -- select datediff(d, '1/1/2002', '1/1/2020')
BEGIN
	INSERT INTO HC_Prod01.dbo.dss_TimeX
	(
		FullDatedte,
		DayText,
		MonthTxt,
		YearNum,
		DayOfMonthNum,
		WeekOfYearNum,
		MonthOfYear,
		QuarterTxt
	)
	SELECT
		dbo.ToDateOnly(@Today),
		DATENAME(dw, @Today),
		DATENAME(mm, @Today),
		DATEPART(yy, @Today),
		DATEPART(d, @Today),
		DATEPART(ww, @Today),
		DATEPART(m, @Today),
		CASE DATEPART(m, @Today)
			WHEN 1 THEN 'Q1'
			WHEN 2 THEN 'Q1'
			WHEN 3 THEN 'Q1'
			WHEN 4 THEN 'Q2'
			WHEN 5 THEN 'Q2'
			WHEN 6 THEN 'Q2'
			WHEN 7 THEN 'Q3'
			WHEN 8 THEN 'Q3'
			WHEN 9 THEN 'Q3'
			WHEN 10 THEN 'Q4'
			WHEN 11 THEN 'Q4'
			WHEN 12 THEN 'Q4'
		END
	SET @Counter = @Counter + 1
	SET @Today = DATEADD(d, 1, @Today)
END