SQL Server Portal

Few days ago, I was working on a report and came across with an issue regarding conversion of Short month name to Month name and we do not have any built-in function to do it in SQL Server. So I commenced my research and finally found few solutions.

Before proceeding with the solutions, let me create a sample to demonstrate this solution.

USE tempdb
GO
CREATE TABLE tbl_MonthName
(
[ID] INT,
[SHORT_MONTH_NAME] VARCHAR(3)
)
GO

INSERT INTO tbl_MonthName
SELECT 1, N'Jan' UNION ALL
SELECT 2, N'Feb' UNION ALL
SELECT 3, N'Mar' UNION ALL
SELECT 4, N'Apr' UNION ALL
SELECT 5, N'May' UNION ALL
SELECT 6, N'Jun' UNION ALL
SELECT 7, N'Jul' UNION ALL
SELECT 8, N'Aug' UNION ALL
SELECT 9, N'Sep' UNION ALL
SELECT 10, N'Oct' UNION ALL
SELECT 11, N'Nov' UNION ALL
SELECT 12, N'Dec'
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 short month name and year to make a complete date of that particular month using additional single quotes and then get the Month name from this concatenate date using DATENAME function. Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
[ID]
,[SHORT_MONTH_NAME] AS [SHORT MONTH NAME]
,DATENAME(MONTH,CONVERT(
DATETIME,'1'+ [SHORT_MONTH_NAME] + '1')) AS [MONTH NAME]
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 short 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 Name using FORMAT function. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT
[ID]
,[SHORT_MONTH_NAME] AS [SHORT MONTH NAME]
,FORMAT(CONVERT(
DATETIME,CONCAT(1,[SHORT_MONTH_NAME],1)),'MMMM') AS [MONTH NAME]
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.