How to Convert a Month Number to a Month Name in Sql server

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

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');

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.

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.

 


Leave a Reply

Your email address will not be published. Required fields are marked *