Jeff Moden (9/3/2012)
Be careful now... That uses a "counting Recursive CTE" and they're notorious for bad performance. Please see the following article for why you shouldn't use "counting Recursive CTEs"...
http://www.sqlservercentral.com/articles/T-SQL/74118/
You've also used DATEFROMPARTS, which I believe is a 2012 function, on a 2008 forum.
That's good point, it's going to get a performance hit. Thanks for the info. To avoid this, I would have to use a supplemental table with numbers from 1 to 31, and then do a cross join to manipulate month and day, which will look similiar to your query.