SQL 2008 CTE for date/time dimension table

Here is a quick CTE to get date/time dimension data for a SQL 2008 data warehouse. You could use the built in SQL 2008 time feature but it does not give you hourly granularity so this may come in handy for you if you need that level of detail. You could easily just add a few more DATEPART() calls if you needed minutes etc.

This was originally taken from a post here and I modified it to support hour_of_day.
http://blogs.conchango.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx



---------------------------------
-- Get a list of Dates via CTE --
---------------------------------
with mycte as
(
select cast('2008-01-01' as datetime) fldTimeID
union all
SELECT DATEADD(hh, 1, fldTimeID)
--select DateValue + 1
from mycte
where fldTimeID + 1 < '2040-12-31'
)
select fldTimeID as fldHourID,
DATEADD(dd, DATEDIFF(dd,0,fldTimeID), 0) AS fldDay,
DATEADD(mm, DATEDIFF(mm,0,fldTimeID), 0) as fldMonth,
DATEADD(yy, DATEDIFF(yy,0,fldTimeID), 0) as fldYear,
DATEPART(m, fldTimeID) as month_of_year,
DATEPART(dd, fldTimeID) as day_of_month,
DATEPART(hh, fldTimeID) as hour_of_day
--INTO time
from mycte
OPTION (MAXRECURSION 0)

This will produce 289249 records so bring down the 2040-12-31 date if you don't need it to go out that far.