Convert Month Number to Month Name Function in SQL

1. Overview

When working with dates in SQL, we often store months numerically (1 through 12) for efficiency and standardisation. However, applications need to display the full month names (January-December) for better readability. SQL doesn’t have any in-built function for this conversion but it provides several effective ways to achieve this.

In this tutorial, we’ll explore practical methods to transform numeric month values into their corresponding month names directly within the SELECT queries. We’ll cover built-in functions, formatting options, and alternative approaches that work across different versions of SQL.

Before we proceed, let’s create a table sales_data and insert sample data:

-- Create sample table
CREATE TABLE sales_data (
    id INT,
    month_number INT,
    amount DECIMAL(10,2)
);

-- Insert sample data
INSERT INTO sales_data VALUES
(1, 1, 1500.00), (2, 3, 2200.50), (3, 7, 1895.75), (4, 12, 3100.00);

The above SQL query creates a sample table sales_data and inserts 4 rows. Note that we’ll be using this sample data to demonstrate different SQL queries in this article.

2. Using DATENAME with DATE Construction

The most reliable method that works across all SQL Server versions combines DATENAME with date construction:

SELECT 
    id,
    month_number,
    DATENAME(MONTH, DATEFROMPARTS(2000, month_number, 1)) AS month_name,
    amount
FROM sales_data;

The above SQL query fetches the id, month_number, and amount from the sales_data table that we created earlier. Apart from this, it will also return the corresponding month name (e.g., “January”) derived from the month_number. It uses DATEFROMPARTS and DATENAME to convert the numeric month into its textual representation.

This will generate the following output:

id  month_number  month_name  amount
--- ------------  ----------  ------
1   1             January     1500.00
2   3             March       2200.50
3   7             July        1895.75
4   12            December    3100.00

Since there is no built-in DATENAME function in PostgreSQL and MySQL, the above queries will not work. Alternatively, we can use TO_CHAR() with MAKE_DATE() to achieve similar results:

SELECT 
    id,
    month_number,
    TO_CHAR(MAKE_DATE(2000, month_number, 1), 'Month') AS month_name
FROM sales_data;

In the MySQL server, we can use DATE_FORMAT() with STR_TO_DATE():

SELECT 
    id,
    month_number,
    DATE_FORMAT(STR_TO_DATE(CONCAT('2000-', month_number, '-01'), '%Y-%m-%d'), '%M') AS month_name
FROM sales_data;

Both alternatives mimic SQL Server’s DATENAME logic but use native functions for their respective databases.

3. Using the FORMAT Function

We can also use the FORMAT function to map the month number with the month name:

SELECT 
    id,
    month_number,
    FORMAT(DATEFROMPARTS(2000, month_number, 1), 'MMMM') AS month_name,
    amount
FROM sales_data;

This SQL query will generate the same output table as shown above. Here, we used the FORMAT function, which is more flexible and allows different date formats (e.g., “MMM” for “Jan”). On the other hand, DATENAME is typically faster than FORMAT as it is optimized for extracting date parts.

Note that the FORMAT function is available in SQL Server 12 and later versions only. Since there is no in-built FORMAT function in PostgreSQL and MySQL, we can use the same alternate queries as mentioned previously.

4. Using CASE Statement

Using the CASE statements, we can explicitly map each month number (1-12) to its corresponding month name (January-December). Unlike other methods that rely on SQL Server’s date functions like DATENAME or FORMAT, this method manually defines the relationship between numbers and names.

SELECT 
    id,
    month_number,
    CASE month_number
        WHEN 1 THEN 'January' 
        WHEN 2 THEN 'February' 
        WHEN 3 THEN 'March'
        WHEN 4 THEN 'April' 
        WHEN 5 THEN 'May' 
        WHEN 6 THEN 'June'
        WHEN 7 THEN 'July' 
        WHEN 8 THEN 'August' 
        WHEN 9 THEN 'September'
        WHEN 10 THEN 'October' 
        WHEN 11 THEN 'November' 
        WHEN 12 THEN 'December'
    END AS month_name,
    amount
FROM sales_data;

Note that unlike DATENAME or FORMAT, which are SQL Server-specific, this approach works in all SQL databases. Moreover, using CASE statements, we can add custom names for each month.

5. Creating a Custom MonthName Function

There are scenarios when we need to convert month numbers to names frequently. Instead of writing the same query over and over, it’s recommended to create a custom function. This encapsulates the conversion logic in a reusable function and avoids code duplication, making SQL queries cleaner and more maintainable.

CREATE FUNCTION dbo.MonthName (@month_number INT)
RETURNS VARCHAR(20)
AS
BEGIN
    RETURN DATENAME(MONTH, DATEFROMPARTS(2000, @month_number, 1))
END;

Here, we created a reusable SQL Server function – dbo.MonthName() converts month numbers to full month names. Creating a custom function is slightly slower than direct inline methods, but it improves code readability and centralizes the month name logic for easier maintenance.

In order to use this method, we can run the following query:

SELECT 
    id,
    month_number,
    dbo.MonthName(month_number) AS month_name,
    amount
FROM sales_data;

This will generate a similar output table to the one shown above.

In case of PostgreSQL, we use the following syntax to create a similar custom function:

CREATE OR REPLACE FUNCTION MonthName(month_number INT)
RETURNS VARCHAR(20) AS $$
BEGIN
    RETURN TO_CHAR(DATE '2000-01-01' + (month_number - 1) * INTERVAL '1 month', 'Month');
END;
$$ LANGUAGE plpgsql;

Similarly, we can use the below custom function for the MySQL server:

DELIMITER //

CREATE FUNCTION MonthName(month_number INT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    RETURN DATE_FORMAT(STR_TO_DATE(month_number, '%m'), '%M');
END;
//

DELIMITER ;

We can use the below query to use the custom function in PostgreSQL/MySQL server:

SELECT id,
    month_number,
    MonthName(month_number) AS month_name,
    amount
FROM sales_data;

Note that here, no schema prefix like dbo. is required unless the function resides in a specific schema. The function is called directly using its name.

6. Conclusion

In this tutorial, we discussed different approaches to get the month names using numbers in SQL. First, we discussed the DATENAME approach that provides the best balance of readability and compatibility across SQL Server versions. The FORMAT function, on the other hand, delivers cleaner syntax at a slight performance cost. We also discussed the most portable solution for cross-database compatibility using the CASE statements. Finally, we learned to avoid code duplication by creating a custom function.

As always, the code is available over on GitHub.