Loading Date Dimension

I am sure all of you seen this script before. More than anything I am putting this here for my reference in the future.

Last couple of client sites I have worked, their fiscal year is aligned with calendar year and therefore it made life much easier. The SP below can populate the dates as you specify.


USE [Stage]
GO
CREATE PROCEDURE [dbo].[uspPopulateDate] (@LoadId INT)
AS
DECLARE @id INT
DECLARE @date DATETIME
DECLARE @Startdate DATETIME
DECLARE @Enddate DATETIME
SET @Startdate = '1950-01-01'
SET @EndDate = '2100-12-31'
SET @id = 0
SET @date = DATEADD(dd, @id, @startdate)
BEGIN TRY
BEGIN TRAN -- Start of a transaction
WHILE @date <= @enddate
BEGIN
INSERT
INTO scbDate
SELECT @date CalendarDate,
DATEPART(dd, @date) CalendarDayMonth,
DATEPART(dy, @date) CalendarDayYear,
DATEPART(dw, @date) CalendarDayWeek,
DATENAME(dw, @date) CalendarDayName,
LEFT(DATENAME(dw, @date),3) CalendarAbbDayName,
CASE
WHEN DATEPART(dw, @date) IN (1,7) THEN
'Y'
ELSE
'N'
END CalendarWeekday,
DATEPART(ww, @date) CalendarWeek,
'Week ' + RIGHT('0' + DATENAME(ww, @date), 2) CalendarWeekName,
DATEPART(mm, @date) CalendarMonth,
DATENAME(mm, @date) CalendarMonthName,
LEFT(DATENAME(mm, @date),3) CalendarAbbMonthName,
DATEPART(qq, @date) CalendarQuarter,
'Q' + DATENAME(qq, @date) + ' ' + DATENAME(yy, @date) CalendarQuarterName,
DATEPART(yy, @date) CalendarYear,
DATEPART(dw, @date) FiscalDayWeek,
DATEPART(yy, @date) FiscalYear,
DATEPART(mm, @date) FiscalPeriod,
DATEPART(ww, @date) FiscalWeek,
DATEPART(qq, @date) FiscalQuarter,
0 CurrentDay,
@LoadId LoadId,
'STA' DataSource
SET @id = @id + 1
SET @date = DATEADD(dd, @id, @startdate)
END
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN -- Rollback Transaction
END CATCH

Thanks
Sutha