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