How to convert month number to month name in SQL and Snowflake

There is not a built-in function to convert month number to month name in SQL. But we can use the combination of a few different date functions to achieve this. Different databases have different built-in date functions.

But before we get to that, we’d like to show you a super easy method to get this done.

It’s called “low-code modeling with Datameer.”

1. Datameer:

Datameer is a multi-persona transformation tool built for modeling data within Snowflake.

For our requirement, Datameer has an in-built “Monthname” function that takes your date, extracts the month number, and seamlessly outputs the month name in the appropriate format.

View the video above to see how this is done in Datameer.

2. MySQL:

In MySQL, we can use a combination of functions ‘MONTHNAME’ and ‘STR_TO_DATE’ functions to get a month name from a month number.

SELECT MONTHNAME(STR_TO_DATE(5, '%m')) AS 'Month Name'
-- Output
# Month Name

------------------
May

SELECT MONTHNAME(STR_TO_DATE(11, '%m')) AS 'Month Name'
-- Output
# Month Name

--------------
November

3. SQL SERVER:

In SQL SERVER, we can use a combination of functions ‘DATENAME’ and ‘DATEADD’ functions to get a month name from a month number.

-- In Below query, the 3rd parameter of function DATEADD can be any date with 12th month
SELECT DATENAME(MONTH, DATEADD(MONTH, 1, '2000-12-01')) AS 'Month Name'

-- Output
Month Name
-----------
January

-- In Below query, the 3rd parameter of function DATEADD can be any date with 12th month
SELECT DATENAME(MONTH, DATEADD(MONTH, 2, '2020-12-01')) AS 'Month Name'

-- Output
Month Name
-----------
February

-- Or the 3rd parameter can simply be -1 as below
SELECT DATENAME( MONTH, DATEADD( MONTH, 12, -1)) AS 'Month Name'

-- Output
Month Name
------------------
December

4. 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(1, 'MM'), 'MONTH') AS "Month Name" FROM DUAL;

-- Output
Month Name
------------------
JANUARY 

SELECT TO_CHAR(TO_DATE(3, 'MM'), 'MONTH') AS "Month Name" FROM DUAL;

-- Output
Month Name
------------------
MARCH 

And that’s how to convert month number to month name in SQL!

Do you love how fast and easy this is with Datameer?

To explore more features, click the link to sign up for your free trial today!

Up Next:

Learn How to DELETE data from a table by joining with another table in SQL