SQL SERVER – How to Convert Month Number to Month Name

Working with dates and times is a common task in SQL. You may encounter a situation where you have months stored as integers (from 1 to 12) and you need to convert them to their respective month names (from “January” to “December”). This can be quite useful for generating reports or performing data analysis that’s more readable for human users. Let us learn How to Convert Month Number to Month Name.

Method 1: Using DateName and DateAdd

This method uses SQL Server’s DateName and DateAdd functions. The DateName function returns a character string that represents the specified datepart of the specified date. The DateAdd function returns a specified date with the specified number interval added to the specified datepart of that date.

DECLARE @MonthNumber INT
SET @MonthNumber = 1 -- replace 1 with your month number
SELECT DateName(month, DateAdd(month, @MonthNumber, -1)) AS MonthName

In this statement, we’re adding -1 to a month number, effectively turning the month number into a date, and then extracting the month name from that date.

Method 2: Using Concatenated String and Substring

This method might seem a bit unconventional at first, but it works, and it’s deterministic, meaning it can be used as a computed column too. In this method, we use a concatenated string of abbreviated month names and extract the desired month name using the SUBSTRING function.

DECLARE @MonthNumber INT
SET @MonthNumber = 1 -- replace 1 with your month number
SELECT SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', 
(@MonthNumber * 4) - 3, 3) AS MonthName

Here, we’re using the month number to calculate the starting position in the concatenated string and then extracting a substring of length 3 (the length of the abbreviated month name). (Source: Stackoverflow)

Method 3: Using FORMAT and DATEFROMPARTS

Starting with SQL Server 2012, you can use the FORMAT and DATEFROMPARTS functions to solve this problem. The DATEFROMPARTS function returns a date value for the date, which is created from the specified year, month, and day. The FORMAT function returns a value formatted with the specified format and optional culture.

DECLARE @MonthNumber INT
SET @MonthNumber = 1 -- replace 1 with your month number
SELECT FORMAT(DATEFROMPARTS(1900, @MonthNumber, 1), 'MMMM', 'en-US') AS MonthName

This statement constructs a date from the year 1900 and the provided month number, then formats that date to only show the full month name. If you want a three-letter month, you can use ‘MMM’ instead of ‘MMMM’ in the FORMAT function.

DECLARE @MonthNumber INT
SET @MonthNumber = 1 -- replace 1 with your month number
SELECT FORMAT(DATEFROMPARTS(1900, @MonthNumber, 1), 'MMM', 'en-US') AS MonthName

In conclusion, converting numerical month values to their respective names in SQL Server can be done in a variety of ways. The method you choose will likely depend on your specific needs and the version of SQL Server you’re using.

You can watch my YouTube videos over here.

Reference: Pinal Dave (https://blog.sqlauthority.com)