sql server – How to group by month from Date field using sql

sql server – How to group by month from Date field using sql

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).

Use the DATEPART function to extract the 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)

sql server – How to group by month from Date field using sql

I used the FORMAT function to accomplish this:

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