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

GarethD · Accepted Answer · 2013-01-28T16:07:18.293

149

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

edited Jan 28 '13 at 16:07

answered Jan 28 '13 at 16:01

GarethD

68,045
10
83
123

GarethD, thanks man both method worked fine. Is there a way i can group both the year and the month together in one field? Meaning to show like this format: Dec-12 (Dec is the month and 12 is the year). thanks – user1858332 Jan 28 '13 at 16:27
The first method will do this, you just need to format the column. I'd advise doing this outside of SQL, but if it has to be done as such you can use something like `SELECT STUFF(SUBSTRING(CONVERT(VARCHAR, CURRENT_TIMESTAMP, 6), 4, 6), 4, 1, '-');` – GarethD Jan 28 '13 at 17:04
@GarethD Could you explain how did you just use datediff between 0 and date. 0 is not a date. – irfandar Oct 18 '13 at 19:42
1

and also what does Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0) do why not just DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0) – irfandar Oct 18 '13 at 19:44
3

@irfandar 0 is not a date, but SQL-Server implicitly converts it to 1st January 1900. For your second question `Closing_Date =` is just the column alias, it is the same as having `AS Closing_Date` after the expression. It is entirely subjective but I personally find the `alias =` notation much easier to read than `AS Alias`. For more on why I prefer it [read this article](https://sqlblog.org/2012/01/23/bad-habits-to-kick-using-as-instead-of-for-column-aliases) by Aaron Bertrand. – GarethD Oct 20 '13 at 09:07
Thanks for a really nice solution. – wghornsby Oct 09 '15 at 15:31

score 50 · Answer 2 · answered Jan 28 '13 at 16:00

50

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)

answered Jan 28 '13 at 16:00

Bogdan Gavril MSFT

20,615
10
53
74

20

For Mysql you can use MONTH() or MONTHNAME() instead of DATEPART – frazras Jul 28 '14 at 14:36
If you run this query often, consider to build an appropriate index. – user_0 Dec 02 '14 at 16:28
Plus 1 for simple reply. – berdem Aug 26 '16 at 09:21
13

This would consider months from different years the same -- which is typically NOT was is expected. – ivan_pozdeev Aug 22 '18 at 21:39
This puts a "1" for january etc.. It doesn't tell you the month name – user890332 Sep 12 '19 at 17:18
Adding a date range will prevent data from different years. – Balasubramanian S Dec 29 '20 at 07:45

score 24 · Answer 3 · answered Oct 05 '16 at 01:55

24

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

answered Oct 05 '16 at 01:55

Andrei Sura

2,465
1
20
15

this will not work because you cannot alias in the ORDER BY clause – Jack Armstrong Feb 13 '21 at 21:22

score 17 · Answer 4 · edited Oct 04 '17 at 11:32

17

By Adding MONTH(date_column) in 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

edited Oct 04 '17 at 11:32

Marian Nasry

821
9
22

answered Oct 04 '17 at 10:25

A W

1,041
11
18

score 2 · Answer 5 · answered Apr 23 '18 at 14:18

2

DATEPART function doesn't work on MySQL 5.6, instead use MONTH('2018-01-01')

answered Apr 23 '18 at 14:18

Jordan

319
2
12

John Sonnino · Answer 6 · 2019-11-18T12:16:12.363

2

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 >= '2012-02-01' AND Closing_Date <= '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 -

edited Nov 18 '19 at 12:16

answered Nov 15 '19 at 14:37

John Sonnino

509
5
10

score 2 · Answer 7 · answered Sep 15 '20 at 02:47

SQL Server 2012 version above,

SELECT  format(Closing_Date,'yyyy-MM') as ClosingMonth,
        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 format(Closing_Date,'yyyy-MM'), Category;

score 0 · Answer 8 · edited Nov 06 '18 at 08:10

0

SELECT  to_char(Closing_Date,'MM'), 
        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 Category;

edited Nov 06 '18 at 08:10

Suraj Rao

29,388
11
94
103

answered Nov 06 '18 at 08:08

Nida

1

user1845584 · Answer 9 · 2013-01-28T16:07:13.223

-1

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 >= '2012-02-01' and Closing_Date <= '2012-12-31' 
and Defect_Status1 is not null 
group by Year(Closing_Date), Month(Closing_Date), Category

edited Jan 28 '13 at 16:07

answered Jan 28 '13 at 16:00

user1845584

9
1

This is not valid SQL – Mad Echet Nov 04 '13 at 21:44
Well this is valid but brings unpredictable results as you don't group by the fields that you select. You can have any value in closing date as long as the date and the year are the same. – Mad Echet Nov 05 '13 at 17:28

score -1 · Answer 10 · edited Jun 27 '18 at 13:01

-1

Try the Following Code

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;

edited Jun 27 '18 at 13:01

Thomas

1,445
14
30

answered Jun 27 '18 at 12:33

Antony raj

1

How to group by month from Date field using sql

10 Answers10

Linked

Related