Help With Nested CTE

Hi gang,

I have a challenge, which seems like it is probably trivial, but SQL chops are not up to the task, and I am hoping one of you hot-shot DBAs can throw me a bone!

I have a query that populates an OLAP Time dimension table (basically one row per day of the year over several years). What I want to do is expand that table to include each hour of each day over the time span.

The CTE I am using for the day population is:



Code Snippet
WITH dates(date)
-- A recursive CTE that produce all dates between 2006 and 2057-12-31
AS
(
SELECT cast('2006' AS dateTime) date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
SELECT (date + 1) AS date
FROM dates
WHERE
date < cast('2058' AS dateTime) -1
)






What I wanted to do was something like:


Code Snippet
WITH hours(hr)
AS
(
SELECT (DATEPART(hh,date) hr
UNION ALL
SELECT (hr + 1) AS hr
FROM hours
WHERE hr < 24
)






inserted just after



Code Snippet
FROM dates





in the initial CTE. But from what I have read, it seems as though nested CTEs are not allowed.

Can anyone show me how to do this?

Thanks in advance for ANY insight/input!

Cheers,

Chris