GSquared (1/29/2009)
Ouch! Yeah, that piece of code might work, but it hurts to look at!
;with Numbers (Number) as
(select row_number() over (order by object_id)
from sys.all_objects)
select dateadd(month, number, @StartDate)
from Numbers
where number <= datediff(month, @StartDate, @EndDate)
Ahhhhh.... MUCH better. No loops... no recurrsion in the CTE... nice tight code...
The only thing that may be a problem is that (I believe... haven't tested the code) it looks like the day of the startdate and enddate are preserved instead of the whole month being included. Guess it all depends on what the op actually needs.
--Jeff Moden
Change is inevitable... Change for the better is not.