DECLARE @StartDate DATETIME,
        @EndDate DATETIME,
        @Number INT,
        @YearsAhead INT 

DECLARE @MyDateDimension TABLE (
    DateKey INT,
    DateANSI DATETIME,
    DateText VARCHAR(30),
    DateInMonth INT,
    MonthOfYearNumber INT,
    YearNumber INT,
    DayOfWeekText VARCHAR(20),
    MonthOfYearText VARCHAR(30),
    WeekNumber INT,
    [Quarter] VARCHAR(2),
    Half VARCHAR(2) )

SELECT  @StartDate = {d'1970-01-01'},
        @YearsAhead = 100,
        --Add @YearsAhead to todays date and ensure time is midnight
        @EndDate = DATEADD([DAY],0, DATEDIFF([DAY],0, DATEADD([YEAR],@YearsAhead,GETDATE()))),
        @Number = 0

WHILE DATEADD([DAY],@Number,@StartDate) <= @EndDate

BEGIN

INSERT INTO @MyDateDimension
SELECT
CAST(DATENAME([YEAR],DATEADD([DAY],@Number,@StartDate)) + SUBSTRING(CAST(100 + DATEPART([MONTH],DATEADD([DAY],@Number,@StartDate)) AS VARCHAR(3)),2,2) + SUBSTRING(CAST(100 + DATEPART([DAY],DATEADD([DAY],@Number,@StartDate)) AS VARCHAR(3)),2,2) AS INT) AS DateKey,
DATEADD([DAY],@Number,@StartDate) AS DateANSI,
CAST(DATENAME([DAY],DATEADD([DAY],@Number,@StartDate)) + ' ' + DATENAME(MONTH,DATEADD([DAY],@Number,@StartDate)) + ' ' + DATENAME([YEAR],DATEADD([DAY],@Number,@StartDate)) AS VARCHAR(30))AS DateText,
DATEPART([DAY],DATEADD([DAY],@Number,@StartDate)) AS DateInMonthNumber,
DATEPART([MONTH],DATEADD([DAY],@Number,@StartDate)) AS MonthOfYearNumber,
DATEPART([YEAR],DATEADD([DAY],@Number,@StartDate)) AS YearNumber,
CAST(DATENAME([WEEKDAY],DATEADD([DAY],@Number,@StartDate)) AS VARCHAR(10)) AS [DayOfWeekText],
CAST(DATENAME([MONTH],DATEADD([DAY],@Number,@StartDate)) AS VARCHAR(10)) AS [MonthOfYearText],
DATEPART([WEEK],DATEADD([DAY],@Number,@StartDate)) AS WeekNumber,
'Q' + CAST(DATENAME([QUARTER],DATEADD([DAY],@Number,@StartDate)) AS VARCHAR(10)) AS [Quarter],
'H' + CASE DATEPART([QUARTER],DATEADD([DAY],@Number,@StartDate))
WHEN 1 THEN '1'
WHEN 2 THEN '1'
WHEN 3 THEN '2'
WHEN 4 THEN '2'
END AS [Half]

SET @Number = @Number + 1
END

SELECT * FROM @MyDateDimension