Convert Month Number to Month Name in SQL

In previous article, I have mentioned how to Remove all whitespace from string in SQL Server but now in this article, I have mentioned how to convert month number to month name in SQL Server, MySQL, Oracle database.

In SQL Server (Month name from Month Number)

Let's take a look at SQL Server example first

/*--Declare local variable for testing*/
DECLARE @month_num AS int =5

/*--get month name using DateFromParts*/
select FORMAT(DATEFROMPARTS(1900, @month_num, 1), 'MMMM', 'en-US') AS 'Month Name'

/*--get month name using  'DATENAME' and 'DATEADD' functions */
SELECT DATENAME(MONTH, DATEADD(MONTH, @month_num, '2020-12-01')) AS 'Month Name'

First, we are declaring a local variable to use for example purpose, as "@month_num"

In the second line of query, we are using DateFromParts to provide, year, month number and date, with format and getting result as 'Month Name'.

Similarly, in third line of query, we use a combination of functions 'DATENAME' and 'DATEADD' functions to get a month name from a month number.

Output:

If you want to take current month name, you can directly use below query

select DATENAME(month, getdate())

This will give you output as current month name,i.e, for now output for me "June", as today's date is 8th June.

In MySQL

Now, let's take a look, how we can get month name from month number in MySQL Database.

We can use STR_TO_DATE() to convert the number to a date, and then back with MONTHNAME() to get Month Name in MySQL.

SELECT MONTHNAME(STR_TO_DATE(6, '%m')) AS 'Month Name'; 

Here is the output of above query

In Oracle

In Oracle, we can use a combination of functions ‘TO_CHAR’ and ‘TO_DATE’ functions to get a month name from a month number.

SELECT TO_CHAR(TO_DATE(6, 'MM'), 'MONTH') AS monthname FROM DUAL;

Output

monthname
---------
June

In Postgresql

In Postgresql, you can simply use below query to get month name from number, as Full-Name and Short name.

SELECT TO_CHAR(
    TO_DATE (12::text, 'MM'), 'Month'
    ) AS "Month Name";   
//output -> December (Full-Name)


SELECT TO_CHAR(
    TO_DATE (12::text, 'MM'), 'Mon'
    ) AS "Month Name";

//output ->Dec  (Short Name)

Hope it helps.

You may also like to read:

SQL Comments (Comment in SQL Query)

Delete (Drop) Database in SQL Server

Stored procedure in sql server (With Example)

Difference between Stored procedure and function in SQL Server

SQL Server Management Studio (SSMS) Versions

Best Free SQL Server database hosting