Date Dimension

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Satish Jayanthi » Date Dimension

Date Dimension

Rate Topic

Display Mode

Topic Options

Author

Message

srjayanthi

Posted Sunday, April 20, 2008 2:43 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, March 11, 2013 2:11 PM
Points: 75, Visits: 164

Comments posted to this topic are about the item Date Dimension

Post #487628

ERBIRD

Posted Wednesday, June 04, 2008 6:31 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 15, 2008 9:13 AM
Points: 1, Visits: 22

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

Post #511300