Okay let me thave a go.

Cadavre introduces the concept of a tally table. In his solution he creates the table on the fly which is great but as this numbers table is so useful I highly recommend you create this table permanently in a utility database perhaps?

Here is one way of creating the Tally table:

If exists (select 1 from information_schema.tables where table_name = 'Tally')

drop table dbo.[Tally]

GO

CREATE TABLE dbo.Tally (N INT,CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N))

DECLARE @Counter INT

SET @Counter = 0

WHILE @Counter <= 10000

BEGIN

INSERT INTO dbo.Tally (N)

VALUES (@Counter)

SET @Counter = @Counter + 1

Now that we have our numbers we can get the first day of each month as follows:

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())+N, 0) as N

FROM TALLY

The above bit of code looks complicated but I will try and explain. Our goal is to get one record per month as per yor requirement. To do this we start with:

SELECT DATEDIFF(month, 0, GETDATE())+N

FROM TALLY

This gives us the number of months since 01/01/1900 this is the date you get when you cast the value 0 as a date. So the above query will run somethign like this.

1) Number of months since 01/01/1900 to this day = 1355 + N (tally table starts at 0) so = 1355

2) Number of months since 01/01/1900 to this day = 1355 + N (next value of N is 1 ) so = 1356

and so on.

If we then add the number of months back to 01/01/1900 we will get back 1 record per month

And to get the name we can make the above a derived table or CTE as follows:

select DATENAME(month,N), YEAR(N)

FROM (SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())+N, 0) as N

FROM TALLY) as a

Hope this helps.