How to convert month number to month name in SQL Server?

In SQL Server there are several ways to convert an integer month number to a month name. Let’s see a few of the methods with examples.

Using DATENAME function

Using the DATENAME T-SQL function is the best way to convert month number to name. In this method, we have to use the DATEADD T-SQL function to convert the integer month number to a date. Here is an example:

DECLARE @Month_Number AS INT
SET @Month_Number = 7  /* Month Number */

SELECT DateName( month , DateAdd( month , @Month_Number , -1 ) )

Using FORMAT and DATEFROMPARTS

Another easy way is by using the FORMAT and DATEFROMPARTS T-SQL functions. In this method, a date is generated using the DATEFROMPARTS with the month number and a dummy year and date. Then using the FORMAT function, we can get the name of the month by formatting the date to ‘en-US’ culture. Here is the sample:

DECLARE @Month_Number INT = 10; /* Month Number */

select FORMAT(DATEFROMPARTS(1900, @Month_Number, 1), 'MMMM', 'en-US')
GO;

Using SUBSTRING

Another way to get the month name is by using the SUBSTRING to get the name from a string of fixed length month names. Here in this example, we have generated a string by adding 3 letter month names separated by a space. It is important to have the length of all the month names equal. Then using the SUBSTRING function you can get the name of the month for the input number.

DECLARE @Month_Number INT = 12; /* Month Number */

SELECT SUBSTRING('Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', 
    (@Month_Number * 4) - 3, 3)
GO;

In this method, you should ensure an equal number of characters in the month names. If you require the full name of the month, pad the shorter month names with spaces and trim the spaces afterward.

Using STRING_SPLIT function

This method utilizes the STRING_SPLIT T-SQL function, introduced in SQL Server 2016. While it may not be the most suitable approach to retrieve the Month Name, it is still possible.

DECLARE @Month_Number INT = 10; /* Month Number */
DECLARE @Months AS VARCHAR(100) = 'January February March April May June July August September October November December';

SELECT value AS Month_Name
FROM (
    SELECT value,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM STRING_SPLIT(@Months, ' ')
) AS SplitWords
WHERE RowNum = @Month_Number;

Reference

• More about DATENAME T-SQL function at Microsoft Docs.