Here is a nice use for Common Table Expressions (CTEs) to generate a dates table or other list of stuff. I won't take credit for this technique since I saw it someplace else (but can't remember where). Of course my blog is for stuff I want to remember, so thanks to whoever you are... declare @loops int = 1000 declare @StartDate date = '1/1/2000'; WITH CountTable( RowNumber, [Month], [Year], FirstDay ) AS ( SELECT 1 RowNumber, MONTH(@StartDate) [Month], YEAR(@StartDate) [Year], -- compute the first day of the month from whatever day was provided DATEADD(dd,-(DAY(@StartDate)-1),@StartDate) FirstDay UNION ALL SELECT RowNumber + 1 RowNumber, MONTH(DATEADD(MONTH, 1, FirstDay)), YEAR(DATEADD(MONTH, 1, FirstDay)), DATEADD(MONTH, 1, FirstDay) FirstDay FROM CountTable t WHERE RowNumber The results look like this: 1 1 2000 2000-01-01 2 2 2000 2000-02-01 3 3 2000 2000-03-01 4 4 2000 2000-04-01 5 5 2000 2000-05-01 6 6 2000 2000-06-01 7 ...