## Date Dimension

 Author Message srjayanthi SSC Journeyman Group: General Forum Members Points: 75 Visits: 168 Comments posted to this topic are about the item Date Dimension ERBIRD Forum Newbie Group: General Forum Members Points: 1 Visits: 22 I like a cte better.. something likeWITH 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 GPO Ten Centuries Group: General Forum Members Points: 1054 Visits: 1908 You're both committing unnecessary RBAR. :-)One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.Bertrand Russell RonKyle Ten Centuries Group: General Forum Members Points: 1257 Visits: 3339 For one time populations, there shouldn't be an issue with RBAR.