syntaxhelp.com Your online cheat sheet!

Breaking a day by hours and return 24 rows (one for each hour)

DECLARE @dates TABLE (dt DateTime)
INSERT INTO @dates (dt) SELECT '2009-10-01'

SELECT
	DATEADD(hour, number, dt) AS HourPart
FROM @dates
CROSS JOIN master..spt_values s
WHERE type = 'p' AND number between 0 AND 23

/*
HourPart
-----------------------
2009-10-01 00:00:00.000
2009-10-01 01:00:00.000
2009-10-01 02:00:00.000
2009-10-01 03:00:00.000
2009-10-01 04:00:00.000
2009-10-01 05:00:00.000
2009-10-01 06:00:00.000
2009-10-01 07:00:00.000
2009-10-01 08:00:00.000
2009-10-01 09:00:00.000
2009-10-01 10:00:00.000
2009-10-01 11:00:00.000
2009-10-01 12:00:00.000
2009-10-01 13:00:00.000
2009-10-01 14:00:00.000
2009-10-01 15:00:00.000
2009-10-01 16:00:00.000
2009-10-01 17:00:00.000
2009-10-01 18:00:00.000
2009-10-01 19:00:00.000
2009-10-01 20:00:00.000
2009-10-01 21:00:00.000
2009-10-01 22:00:00.000
2009-10-01 23:00:00.000
*/