How to Convert Month Number in a Date to Month Name in SQL Server

Let us assume I have a Sales table with sales data for each month. Every day sales is stored in the table with columns such date, quantity, price etc. I want to get the total sales for every month. Now since I have a column with date data type, I want to convert the month number in the date to Month Name (like February, March etc.). I’ll show you how this is done in SQL Server.

Here’s my SQL Server table

CREATE TABLE [dbo].[Sales](
    [SalesID] [int] NOT NULL,
    [Quantity] [int] NULL,
    [Price] [numeric](18, 2) NULL,
    [SalesDate] [datetime] NULL,
)

The table has four columns with the SalesDate column with type datetime. You can use the date type too.

Insert some data to the table

INSERT INTO dbo.Sales (SalesID, Quantity, Price, SalesDate)
VALUES (1, 21, 11000, '2017-02-15'),
    (2, 3, 2000, '2017-02-09'),
    (3, 15, 21000, '2017-02-27'),
    (4, 28, 51000, '2017-03-02'),
    (5, 9, 6200, '2017-03-05')

Every row in the table now has quantities sold for the day, along with the price and the date on which the products are sold. The date has months in numbers, however, I wish to convert the month number to name, such as, 2 to February, 3 to March etc.

Get the Month Name from Number

To the convert the month in numbers to month name, I’ll write this query.

SELECT DATENAME (MONTH, DATEADD(MONTH, MONTH(SalesDate) - 1, '1900-01-01')) Month, 
	SUM(Quantity) Qantities_Sold, SUM(Price) Amount 
FROM dbo.Sales 
GROUP BY MONTH(SalesDate)

The Output

Convert Month Number to Month Name in SQL Server

In the above query, I am using the in-built function DATENAME() to get the string of the specified datepart (such as MONTH) of a specified date.

Syntax DATENAME

DATENAME ( datepart, date )

For example, to get the current day, month and year you can use the following query using DATENAME function.

select DATENAME (DAY, GETDATE()) Today,
    DATENAME (MONTH, GETDATE()) Current_Month,
    DATENAME (YEAR, GETDATE()) Curreny_Year

Using the DATENAME() function alone, I can convert the month number in each date to month name. Like this,

SELECT DATENAME(MONTH, SalesDate) Month FROM dbo.Sales

However, it won’t give the desired result (see image above) when I add the SUM() function to get the total price and quantities sold for each month.

Now here’s what I’ll do. I add the DATEADD() function along with the DATENAME() function. The in-built function DATEADD() adds a specified time interval from a date. Therefore, when used with DATENAME() function I’ll get this result.

SELECT DATENAME (MONTH, DATEADD(MONTH, MONTH(SalesDate) - 1, '1900-01-01')) Months
FROM dbo.Sales

SQL Server Built-in Functions DateName() with DateAdd()

Finally, I can use the MONTH (SalesDate) in my query’s GROUP BY clause to get the total price and quantity for each month (without repeating the months).

That’s it. Hope this is useful.

Thanks for reading ☺.

← PreviousNext →