The best way to do that is to build a calendar table.
The second best way is to use a numbers table.
After that, it's a numbers CTE.
After that, recursion.
Are you in a position where you can build a calendar table? It's just a table of dates, starting from whatever you need and ending whenever you need. For example, a table of dates between 1 Jan 2000 and 31 Dec 2050, is a default that I use quite often. You can then store in it things like which dates are holidays, weekends, etc.
If you have a numbers table, you can do a query where you use DateAdd(month) on the numbers table, with the first date being what you add to.
If you don't have either of those, and can't build either one, you might be able to build a numbers CTE, where you select row_number in a CTE, and then use that just like a numbers table.
If none of those are possible, build a While loop and insert into a temp table (or a table variable if it needs to be a UDF), or build a recursive CTE with a Union All operator.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon