GenerateDays.sql

DECLARE @day datetime
SET @day = '18000101'

DECLARE @dimDaysVar TABLE (
	[DayKey] int,
	[Year] smallint,
	[Month] tinyint,
	[Day] tinyint,
	[DayOfWeek] tinyint,
	[Week] tinyint,
	[Quarter] tinyint,
	[YearMonth] int,
	[YearWeek] int,
	[YearQuarter] int
)

WHILE (@day < '22000101')
BEGIN
	INSERT INTO @dimDaysVar ([DayKey], [Year], [Month], [Day], [DayOfWeek], [Week], [Quarter], [YearMonth], [YearWeek], [YearQuarter])
	VALUES (
		DATEPART(YEAR, @day) * 10000 + DATEPART(MONTH, @day) * 100 + DATEPART(DAY, @day),
		DATEPART(YEAR, @day),
		DATEPART(MONTH, @day),
		DATEPART(DAY, @day),
		DATEPART(WEEKDAY, @day),
		DATEPART(ISO_WEEK, @day),
		DATEPART(QUARTER, @day),
		DATEPART(YEAR, @day) * 100 + DATEPART(MONTH, @day),
		IIF(DATEPART(ISO_WEEK, @day) > 50 AND DATEPART(MONTH, @day) = 1, (DATEPART(YEAR, @day) - 1) * 100 + DATEPART(ISO_WEEK, @day), DATEPART(YEAR, @day) * 100 + DATEPART(ISO_WEEK, @day)),
		DATEPART(YEAR, @day) * 10 + DATEPART(QUARTER, @day))
	SET @day = DATEADD(day, 1, @day)
END

INSERT INTO [dbo].[DimDays] SELECT * FROM @dimDaysVar