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.
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.
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, 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;
monthname --------- June
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: