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  gt    2012-02-01  and Closing Date  lt    2012-12-31  and Defect Status1 is not null group by  Closing Date  Category

User · Answer

Try the Following Code  SELECT  Closing Date   DATEADD MONTH  DATEDIFF MONTH  0  Closing Date   0            Category            COUNT Status  TotalCount  FROM    MyTable WHERE   Closing Date  gt    2012-02-01   AND     Closing Date  lt    2012-12-31  AND     Defect Status1 IS NOT NULL GROUP BY DATEADD MONTH  DATEDIFF MONTH  0  Closing Date   0   Category

User · Answer

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

User · Answer

You can do this by using Year    Month   Day   and datepart     In you example this would be   select  Closing Date  Category   COUNT Status TotalCount from  MyTable where Closing Date  gt    2012-02-01  and Closing Date  lt    2012-12-31   and Defect Status1 is not null  group by Year Closing Date   Month Closing Date   Category

User · Answer

By Adding MONTH date column  in GROUP BY   SELECT Closing Date  Category   COUNT Status TotalCount FROM   MyTable WHERE  Closing Date  gt    2012-02-01  AND Closing Date  lt    2012-12-31  AND    Defect Status1 IS NOT NULL GROUP BY MONTH Closing Date   Category

User · Answer

I would use this   SELECT  Closing Date   DATEADD MONTH  DATEDIFF MONTH  0  Closing Date   0            Category            COUNT Status  TotalCount  FROM    MyTable WHERE   Closing Date  gt    2012-02-01   AND     Closing Date  lt    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  gt    2012-02-01   AND     Closing Date  lt    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

User · Answer

DATEPART function doesn t work on MySQL 5 6  instead use MONTH  2018-01-01

User · Answer

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  gt    2011-12-01   AND Closing Date  lt   2016-07-01   GROUP BY FORMAT Closing Date   yyyy MM   ORDER BY Closing Month

User · Answer

Try this   select min closing date   date part  month  closing date      -     date part  year  closing date  AS month  Category  COUNT Status TotalCount  FROM MyTable where Closing Date  gt    2012-02-01  AND Closing Date  lt    2012-12-31  AND Defect Status1 is not null GROUP BY month  Category  ORDER BY 1   This way you are grouping by a concatenated date format  joined by a -

User · Answer

SQL Server 2012 version above  SELECT  format Closing Date  yyyy-MM   as ClosingMonth          Category            COUNT Status  TotalCount  FROM    MyTable WHERE   Closing Date  gt    2012-02-01   AND     Closing Date  lt    2012-12-31  AND     Defect Status1 IS NOT NULL GROUP BY format Closing Date  yyyy-MM    Category

User · Answer

SELECT  to char Closing Date  MM             Category            COUNT Status  TotalCount  FROM    MyTable WHERE   Closing Date  gt    2012-02-01   AND     Closing Date  lt    2012-12-31  AND     Defect Status1 IS NOT NULL GROUP BY Category

[sql] How to group by month from Date field using sql

Examples related to sql

Examples related to sql-server

Examples related to tsql