Convert Month Number to Month Name Function in SQL

Question

Hi, I have months stored in SQL Server as 1,2,3,4,...12. I would like to display them as January,February etc. Is there a function in SQL Server like MonthName(1) = January? I am trying to avoid a CASE statement, if possible.

Thanks

Edit ~ I am on SQL Server 2005, if this makes a difference.

Answer 1

A little hacky but should work:

SELECT DATENAME(month, DATEADD(month, @mydate-1, CAST('2008-01-01' AS datetime)))

Answer 2

In some locales like Hebrew, there are leap months dependant upon the year so to avoid errors in such locales you might consider the following solution:

SELECT DATENAME(month, STR(YEAR(GETDATE()), 4) + REPLACE(STR(@month, 2), ' ', '0') + '01')

Answer 3

I think this is the best way to get the month name when you have the month number

Select DateName( month , DateAdd( month , @MonthNumber , 0 ) - 1 )

Answer 4

You can use the inbuilt CONVERT function

select CONVERT(varchar(3), Date, 100) as Month from MyTable.

This will display first 3 characters of month (JAN,FEB etc..)

Answer 5

Here is a post on it. http://praveenbattula.blogspot.com/2009/04/how-to-know-month-name-from-month-index.html

Answer 6

SELECT DATENAME(month, GETDATE()) AS 'Month Name'