How to convert a month number to a month name in Sql Server
Sometimes you have a month number,probably this number was extracted from a date, you want to display that month number as amonth name.
On the following article we are going to show you how to do it with on multiple ways and a performance comparison between these methods.
Ways to Convert a Month Number to a Month Name in Sql Server Table
1. Demo Data
For this example we are going to create a table that contains a list of employees and their birthdays.
/*DEMO TABLE*/ CREATE TABLE Employees( ID INT NOT NULL ,NAME VARCHAR(250) NOT NULL ,BIRTHDAY DATE NOT NULL ,BIRTHMONTH_F1 VARCHAR(250) NOT NULL ,BIRTHMONTH_F2 VARCHAR(250) NOT NULL ); /*DEMO DATA, DATES ARE IN YYYYMMDD FORMAT*/ INSERT INTO Employees(ID,NAME,BIRTHDAY,BIRTHMONTH_F1,BIRTHMONTH_F2 ) VALUES(1,'Richard','19870105','1','01') ,(2,'Eduard','19840203','2','02') ,(3,'John','19880321','3','03') ,(4,'Sam','19820406','4','04') ,(5,'Lois','19810505','5','05') ,(6,'Vanessa','19740612','6','06') ,(7,'Beryl','19890711','7','07') ,(8,'Lizy','19600810','8','08') ,(9,'Charles','19700925','9','09') ,(10,'Aqueel','19781029','10','10') ,(11,'Deepak','19621112','11','11') ,(12,'Shawn','19901201','12','12');
2. Obtaining Month Number from a date
Obtaining a month number from a date consist in applying a function called datepart. This function returns that part as you can see in the following example.
SELECT NAME ,BIRTHDAY ,DATEPART([MONTH],BIRTHDAY) AS TEST_NUM_MONTH FROM Employees
The following query is going to return month numbers for our Birthday column.
This query returns month number by using the convert function.
SELECT NAME ,BIRTHDAY ,CONVERT(VARCHAR(2),BIRTHDAY,1) AS TEST_NUM_MONTH FROM Employees
the last query returns month in two digits format.
3. Methods for converting a month number to month name
As you’ve seen in the previous example, month number formats are not always the same, for January you can have 1, 01 or other.
In the following examples we are going to see those examples in both of them we are going to use .
Method 1: Using Concat with Datename function
Datename function requires to have a date to return the month name, a workaround for this consist in concatenating year and day to the month number.
This means that if we have 05 as month number, we are going to concatenate it with ‘1900’ year and with ’01’ day, this will be ‘1900501’, datename function is going to return May.
SELECT BIRTHDAY ,DATENAME(mm,BIRTHDAY) as BirthMonthName ,BIRTHMONTH_F1 ,DATENAME(mm,CONCAT('1900' ,FORMAT(CAST(BIRTHMONTH_F1 AS INT),'00'),'01')) AS BirthMonthName_F1 ,BIRTHMONTH_F2 ,DATENAME(mm,CONCAT('1900',BIRTHMONTH_F2,'01')) as BirthMonthName_F2 FROM Employees
Take this in count for this example.
- We are using FORMAT in BirthMonthName_F1 to give two digits to that month number, instead of 5 we are going to use 05.
- If we don’t use FORMAT function, query execution is going to fail as concatenation is going to end with something like ‘1900501’.
Method 2: Using Dateadd with Datename function
In this example month number format does not affect at all, We can have 01, 001 or 1 and this function is going to return the same results.
SELECT BIRTHDAY ,DATENAME(mm,BIRTHDAY) as BirthMonthName ,BIRTHMONTH_F1 ,DATENAME(MONTH, DATEADD(MONTH ,BIRTHMONTH_F1 -1, CAST('19000101' AS datetime))) AS BirthMonthName_F1 ,BIRTHMONTH_F2 ,DATENAME(MONTH, DATEADD(MONTH ,BIRTHMONTH_F2 -1, CAST('19000101' AS datetime))) AS BirthMonthName_F2 FROM Employees
Take this in count for this example
- We are adding month number to a date, in this case we are using ‘19000101’ and subtracting 1.
- The reason for subtracting is because we have 01 as month and we are adding expected month, for example if we have 05 as datemonth when adding months we are going to have 01+05 and that will return June instead of May.
Method 3: Using Case when function
For this example we are going to use Case/When function for assigning a month name for each month number, For example we are going to use 01 and 1 for January.
SELECT BIRTHMONTH_F1 ,CASE WHEN BIRTHMONTH_F1 = 1 THEN 'January' WHEN BIRTHMONTH_F1 = 2 THEN 'February' WHEN BIRTHMONTH_F1 = 3 THEN 'March' WHEN BIRTHMONTH_F1 = 4 THEN 'April' WHEN BIRTHMONTH_F1 = 5 THEN 'May' WHEN BIRTHMONTH_F1 = 6 THEN 'June' WHEN BIRTHMONTH_F1 = 7 THEN 'July' WHEN BIRTHMONTH_F1 = 8 THEN 'August' WHEN BIRTHMONTH_F1 = 9 THEN 'September' WHEN BIRTHMONTH_F1 = 10 THEN 'October' WHEN BIRTHMONTH_F1 = 11 THEN 'November' WHEN BIRTHMONTH_F1 = 12 THEN 'December' ELSE 'OTHER' END AS BirthMonthName_F1 ,BIRTHMONTH_F2 ,CASE WHEN BIRTHMONTH_F2 = 1 THEN 'January' WHEN BIRTHMONTH_F2 = 2 THEN 'February' WHEN BIRTHMONTH_F2 = 3 THEN 'March' WHEN BIRTHMONTH_F2 = 4 THEN 'April' WHEN BIRTHMONTH_F2 = 5 THEN 'May' WHEN BIRTHMONTH_F2 = 6 THEN 'June' WHEN BIRTHMONTH_F2 = 7 THEN 'July' WHEN BIRTHMONTH_F2 = 8 THEN 'August' WHEN BIRTHMONTH_F2 = 9 THEN 'September' WHEN BIRTHMONTH_F2 = 10 THEN 'October' WHEN BIRTHMONTH_F2 = 11 THEN 'November' WHEN BIRTHMONTH_F2 = 12 THEN 'December' ELSE 'OTHER' END AS BirthMonthName_F2 FROM Employees
As you can see dateformat does not affect case when function, It is returning the same results for both formats.
Try to use the first and the second method which consist in adding/concatenating a date, as these methods does not involve hardcoding a month name. In case you want a more customizable method try to use case/when method.