Convert Month Number to Month Name Function in SQL

Question

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.

Answer 1

Use the Best way

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

Answer 2

to_char(to_date(V_MONTH_NUM,'MM'),'MONTH')

where V_MONTH_NUM is the month number

SELECT to_char(to_date(V_MONTH_NUM,'MM'),'MONTH')  from dual;

Answer 3

Use this statement to convert Month numeric value to Month name.

SELECT CONVERT(CHAR(3), DATENAME(MONTH, GETDATE()))

Answer 4

Starting with SQL Server 2012, you can use FORMAT and DATEFROMPARTS to solve this problem.

select FORMAT(DATEFROMPARTS(1900, @month_num, 1), 'MMMM', 'en-US')

If you want a three-letter month:

select FORMAT(DATEFROMPARTS(1900, @month_num, 1), 'MMM', 'en-US')

If you really want to, you can create a function for this:

CREATE FUNCTION fn_month_num_to_name
(
    @month_num tinyint
)
RETURNS varchar(20)
AS
BEGIN
    RETURN FORMAT(DATEFROMPARTS(1900, @month_num, 1), 'MMMM', 'en-US')
END

Answer 5

you can get the date like this. eg:- Users table

id name created_at
1  abc  2017-09-16
2  xyz  2017-06-10

you can get the monthname like this

select year(created_at), monthname(created_at) from users;

output

+-----------+-------------------------------+
| year(created_at) | monthname(created_at)  |
+-----------+-------------------------------+
|      2017        | september              |
|      2017        | june                   |

Answer 6

This one worked for me:

@MetricMonthNumber (some number)

SELECT 
(DateName( month , DateAdd( month , @MetricMonthNumber - 1 , '1900-01-01' ) )) AS MetricMonthName
FROM TableName

From a post above from @leoinfo and @Valentino Vranken. Just did a quick select and it works.