In just a couple of years, this table is only going to contain past dates.

If you're going to create a calendar table, it should contain more years than this, I would have thought - or be dynamically created as and when required spanning enough years before and after the current date.

Also, Lowell's suggestion is needlessly complicated - the months have the same name every year, so there is no need to provide a variable year in the MonthName calculation:

selectt.Number as [year]

,m.[month]

,DateName(month,DateAdd(month,(m.[month]-1),0)) as MonthName

fromtally t

cross join(values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) m([month])

wheret.Number between DatePart(year,GetDate()) - 100 and DatePart(year,GetDate()) + 200

Of course, this assumes that you have a Tally table handy, but if you haven't, then you should! 😉