Date Dimension

  • Comments posted to this topic are about the item Date Dimension

  • I like a cte better.. something like

    WITH mycte

    AS (

    SELECT

    CAST('2000-01-1' AS DATETIME) DateValue

    UNION ALL

    SELECT

    DateValue + 1

    FROM

    mycte

    WHERE

    DateValue + 1 < '2049-12-31'

    )

    INSERT INTO

    [dbo].[DimDate]

    (

    [Date]

    , [DayNumberOfYear]

    , [DayNameOfWeek]

    , [DayOfWeek]

    , [DayOfMonth]

    , [WeekNumberOfYear]

    , [MonthNumberOfYear]

    , [MonthName]

    , [CalendarQuarter]

    , [CalendarYear]

    , [QuarterName]

    )

    SELECT

    DateValue AS [DATE]

    , DATEPART(dy, DateValue) [day of year]

    , DATENAME(dw, DateValue) [Day]

    , DATEPART(dw, DateValue - 1) [day of week]

    , DATEPART(dd, DateValue) [day of month]

    , DATEPART(ww, DateValue) [week]

    , DATEPART(mm, DateValue) [month]

    , DATENAME(mm, DateValue) [month]

    , DATEPART(qq, DateValue) [quarter]

    , DATEPART(yy, DateValue) [year]

    , 'Q' + CAST(DATEPART(qq, DateValue) AS CHAR(1)) [quarterName]

    FROM

    mycte

    OPTION

    ( MAXRECURSION 0 )

    will generate the table in one statement and seems to be fairly fast

  • You're both committing unnecessary RBAR.

    ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

  • For one time populations, there shouldn't be an issue with RBAR.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply