SQL Server Portal

A few days ago, I wrote an article about Multiple ways to convert month number to month name. Recently, I received a query inquiring how to convert Month name to Month number.

Let me create a sample to demonstrate this solution.

USE tempdb
GO
CREATE TABLE tbl_MonthName
(
[ID] INT,
[MONTH_NAME] VARCHAR(50)
)
GO

INSERT INTO tbl_MonthName
SELECT 1, N'January' UNION ALL
SELECT 2, N'February' UNION ALL
SELECT 3, N'March' UNION ALL
SELECT 4, N'April' UNION ALL
SELECT 5, N'May' UNION ALL
SELECT 6, N'June' UNION ALL
SELECT 7, N'July' UNION ALL
SELECT 8, N'August' UNION ALL
SELECT 9, N'September' UNION ALL
SELECT 10, N'October' UNION ALL
SELECT 11, N'November' UNION ALL
SELECT 12, N'December'
GO

SELECT * FROM tbl_MonthName
GO
--OUTPUT

Method 1 :
In this method, we will use the Old approach. In this approach, we need to manually concatenate day with month name and year to make a complete date of that particular month using additional single quotes and then get the Month number from this concatenate date using MONTH function. Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
[ID]
,[MONTH_NAME]
,MONTH('1' + [MONTH_NAME] +'00') AS [MONTH_NUMBER]
FROM tbl_MonthName
GO
--OUTPUT

Method 2 :
In this method, we will use the New approach. In this approach, we need to automatically concatenate day with month name and year to make a complete date of that particular month using CONCAT function (without any single quotes) and then get the Month number from this concatenate date using MONTH function. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT
[ID]
,[MONTH_NAME]
,MONTH(CONCAT(1,[MONTH_NAME],0)) AS [MONTH_NUMBER]
FROM tbl_MonthName
GO
--OUTPUT

Conclusion :
In both methods, we used different approaches using manual concatenation and automatic concatenation using CONCAT function. I would recommend Method 2 because in my earlier article I found that  CONCAT function is much faster than the normal concatenations.