Convert Month Number to Month Name Function in SQL

sql

date-manipulation

sql-functions

dynamic-sql

byAlex Kataev·Aug 25, 2024

To convert a month number to a name in SQL, go for the DATENAME and DATEFROMPARTS approach in SQL Server:

-- SQL Server
SELECT DATENAME(month, DATEFROMPARTS(2023, @MonthNumber, 1)) AS MonthName;

In MySQL and Oracle, use MONTHNAME and TO_CHAR respectively:

-- MySQL
SELECT MONTHNAME(STR_TO_DATE(@MonthNumber, '%m')) AS MonthName;

-- Oracle
SELECT TO_CHAR(TO_DATE(@MonthNumber, 'MM'), 'Month') AS MonthName;

Just drop in your @MonthNumber. Each nugget of code is tailored for specific SQL database engines.

Handling odd cases

SQL isn't always straightforward. Some unusual requirements or constraints might need clever solutions.

Roll up sleeves for some no-date work

What if we simply wish to extract the month's name without an actual date? Enter the 'I'm-a-programmer' hack that uses a fixed reference date:

-- SQL Server
SELECT DATENAME(month, DATEADD(month, @MonthNumber - 1, '19000101')) AS MonthName;
-- Who needs a 1900 calendar anyway? 😂

Short names being fancy

For abbreviated month names like 'Jan', or 'Feb', use substring manipulation:

-- SQL Server
SELECT SUBSTRING(CONVERT(varchar, DATEADD(month, @MonthNumber - 1, '19000101'), 107), 1, 3) AS ShortMonthName;
-- Short, like my patience with "SELECT *"

Feng Shui and other arrangements

Facing an unusual fiscal year beginning in, say April? No sweat, SQL's got your back:

-- SQL Server
SELECT DATENAME(month, DATEADD(month, (@MonthNumber + 3) % 12, '19000101')) AS FiscalMonthName;
--The SQL version of "fiscal balance" 🤣

Remember, these solutions showcase the adaptability of SQL functions in varying date handling situations.

Taking it up a notch

Making SQL multilingual

Language-aware functions like DATENAME behave based on the set language. So, if you require different languages for month names, remember to set your language:

-- SQL Server
SET LANGUAGE French;
SELECT DATENAME(month, @YourDate) AS MonthName;
--Voila! French toast for breakfast today? 🍞

Becoming a SQL 'conjurer'

For more flexibility, conjure your SQL queries programmatically with dynamic SQL:

-- SQL Server
DECLARE @sql NVARCHAR(MAX) = N'SELECT DATENAME(month, ''1900-'' + CAST(@MonthNumber AS VARCHAR) + ''-01'') AS MonthName';
EXEC sp_executesql @sql, N'@MonthNumber INT', @MonthNumber = 5;
--Just a regular SQL spell cast 😉

Embracing locales and diversity

If your app caters to multiple locales, celebrate diversity with locale-specific date formats:

-- SQL Server
SELECT FORMAT(DATEFROMPARTS(2023, @MonthNumber, 1), 'MMMM', 'ja-JP') AS JapaneseMonthName;
--Because SQL is a worldly citizen too 🌍

Always on guard: Handling potential errors

And what happens when @MonthNumber is a rebel? Ensure your code is up to the task and verifies input values:

-- SQL Server
IF @MonthNumber BETWEEN 1 AND 12
    SELECT DATENAME(month, DATEFROMPARTS(2023, @MonthNumber, 1)) AS MonthName;
ELSE
    RAISERROR('This is not the month you are looking for...', 16, 1);
--Jedi SQL tricks... may the force be with you 🌠