How to group by month from Date field using sql

Question

How can I group only by month from the date field (and not by day)?

Here's what my date field looks like:

2012-05-01 

Here is my current SQL:

 select Closing_Date, Category, COUNT(Status)TotalCount from MyTable where Closing_Date >= '2012-02-01' and Closing_Date <= '2012-12-31' and Defect_Status1 is not null group by Closing_Date, Category 

Answer 1

I would use this:

 SELECT Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), Category, COUNT(Status) TotalCount FROM MyTable WHERE Closing_Date >= '2012-02-01' AND Closing_Date <= '2012-12-31' AND Defect_Status1 IS NOT NULL GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), Category; 

This will be grouped by the first of each month, therefore

 `DATEADD(MONTH, DATEDIFF(MONTH, 0, '20130128'), 0)` 

will give '20130101' . I usually prefer this method as it saves dates as dates.

Alternatively, you can use something like this:

 SELECT Closing_Year = DATEPART(YEAR, Closing_Date), Closing_Month = DATEPART(MONTH, Closing_Date), Category, COUNT(Status) TotalCount FROM MyTable WHERE Closing_Date >= '2012-02-01' AND Closing_Date <= '2012-12-31' AND Defect_Status1 IS NOT NULL GROUP BY DATEPART(YEAR, Closing_Date), DATEPART(MONTH, Closing_Date), Category; 

It really depends on your desired result. (The closing year is not needed in your example, but if the date range crosses the year, it could be).

Answer 2

Use the DATEPART function to retrieve a month from the date.

So you would do something like this:

 SELECT DATEPART(month, Closing_Date) AS Closing_Month, COUNT(Status) AS TotalCount FROM t GROUP BY DATEPART(month, Closing_Date) 

Answer 3

I tried the solution user1845584. sql gave an error in the Category field. So I deleted it and it worked. I also added:

 select Year(Closing_Date), Month(Closing_Date)" 

It was better. This means that I transferred the bill according to the year and month.

Answer 4

I used FORMAT to do the following:

 select FORMAT(Closing_Date, 'yyyy_MM') AS Closing_Month , count(*) cc FROM MyTable WHERE Defect_Status1 IS NOT NULL AND Closing_Date >= '2011-12-01' AND Closing_Date < '2016-07-01' GROUP BY FORMAT(Closing_Date, 'yyyy_MM') ORDER BY Closing_Month 

Answer 5

By adding MONTH(date_column) to GROUP BY .

 SELECT Closing_Date, Category, COUNT(Status)TotalCount FROM MyTable WHERE Closing_Date >= '2012-02-01' AND Closing_Date <= '2012-12-31' AND Defect_Status1 IS NOT NULL GROUP BY MONTH(Closing_Date), Category 

Answer 6

You can do this using Year (), Month () Day () and datepart ().

In your example, this would be:

 select Closing_Date, Category, COUNT(Status)TotalCount from MyTable where Closing_Date >= '2012-02-01' and Closing_Date <= '2012-12-31' and Defect_Status1 is not null group by Year(Closing_Date), Month(Closing_Date), Category