Convert Month Number to Month Name in SQL Server

One of my blog readers mailed me asking a simple way to convert a month number to month name. Here’s the simplest way in my opinion:

DECLARE @Mth smallint
SET @Mth = 11
SELECT DateName(mm,DATEADD(mm,@Mth,-1)) as [MonthName]

OUTPUT

Similarly if you want to list all the month names for a year using a T-SQL statement, you can do this:

SELECT Number + 1 as [MonthNumber],
DateName(mm,DATEADD(mm,Number,0)) as [MonthName]
FROM master..spt_values
WHERE Type = 'P' and Number < 12

OUTPUT