Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – Multiple ways to convert month number to month name’

I came across this issue, how to get month name while only month number is stored in the database ?

Given below are the different solutions to the above concern.

Let me create a sample to demonstrate it.

USE tempdb
GO
CREATE TABLE tbl_MonthNumber
(
 [ID] INT IDENTITY(1,1),
 [MONTH_NUMBER] INT
)
GO

INSERT INTO tbl_MonthNumber
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12

GO
SELECT * FROM tbl_MonthNumber
GO
--OUTPUT

Convert_Month_no_to_Name.1.1

Given below are two different methods to convert month number to month name.

Method 1:
This method is compatible with SQL Server 2005 and above.

USE tempdb 
GO 
SELECT [MONTH_NUMBER] 
,DATENAME(MONTH,DATEADD(MONTH, [MONTH_NUMBER] , 0 )-1) 
AS [MONTH_NAME] 
FROM tbl_MonthNumber
GO
--OUTPUT

Convert_Month_no_to_Name.1.2

Method 2:
This method is compatible with SQL Server 2012 and above.

USE tempdb
GO
SELECT [MONTH_NUMBER]
,DATENAME(MONTH,DATEFROMPARTS(1900, [MONTH_NUMBER] , 1 ))
AS [MONTH_NAME]
FROM tbl_MonthNumber
--OUTPUT

convert_monthno_to_name1.3

Read Full Post »