You can use the T-SQL code below to convert a month number to its corresponding name in SQL Server.

This is for times where you don’t actually have the full date – when all you have is the month number. If you do have the date, then here’s how to get the month name from a date.

Example

You can get the month name from its corresponding number by using the DATENAME() function in conjunction with DATEADD().

Here’s an example that uses month 10 (October):

SELECT DATENAME( 
    month, 
    DATEADD( month , 10, -1 )
    );

Result:

October

Explanation of the Code

If you’re wondering why there’s a -1 in the above code, it’s because the base date is 1900-01-01 (well, 1900-01-01 00:00:00.000 to be precise).

If we add 10 to 01, then we get 11, which is November (the wrong month). Therefore we need to subtract it by 1.

The following example should illustrate this better than my words. Here are various DATEADD() values that are returned, depending on what I use as the third argument.

SELECT 
  DATEADD( month, 0, 0 ) AS [Base Date],
  DATEADD( month, 10, 0 ) AS [Add 10],
  DATEADD( month, 10, -1 ) AS [Subtract 1];

Result:

 +-------------------------+-------------------------+-------------------------+
 | Base Date               | Add 10                  | Subtract 1              |
 |-------------------------+-------------------------+-------------------------|
 | 1900-01-01 00:00:00.000 | 1900-11-01 00:00:00.000 | 1900-10-31 00:00:00.000 |
 +-------------------------+-------------------------+-------------------------+ 

So the third option gives us the correct month number, and it’s then a simple matter of using DATENAME() to convert it into the month name.

An Alternative: FORMAT()

If you don’t like the DATENAME() function, you can swap it for the FORMAT() function instead. The same concept applies.

SELECT FORMAT( 
    DATEADD( month , 10, -1 ),
    'MMMM'
    );

Result:

October