How to group by month from Date field using sql

Question

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

Here is 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 group by the first of every month, so

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

will give '20130101'. I generally prefer this method as it keeps dates as dates.

Alternatively you could 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 what your desired output is. (Closing Year is not necessary in your example, but if the date range crosses a year boundary it may be).