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)
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.
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
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. If you have another of doing this, you can share the solution here on my blog.
Thanks for reading.