MySQL query to return a list of months

Question

Is there a way to get a list of months back if a table has this structure:

Table
id    | payment  | date
1001    200.00     2013-10-11
1001    100.00     2013-11-02
1002    250.00     2014-01-23
1003    320.00     2014-02-02
1004    300.00     2014-03-04
1004     90.00     2014-03-05
1004     50.00     2014-04-21
1005    400.00     2014-04-21

I want to get a list back where the month is unique like so:

Months
2013-10-01
2013-11-01
2014-01-01
2014-02-01
2014-03-01
2014-04-01

Is that doable? Or would I have to have some other kind of query and play with it in PHP?

If you have a Calendar table (essentially, a table with all dates in it, plus a few related attributes split off) this becomes trivial. Do you need any _other_ information from this table, or you just want the list of dates? — Clockwork-Muse, Apr 13 '14 at 03:37

score 7 · Accepted Answer · answered Apr 13 '14 at 02:55

7

SELECT DATE_FORMAT(date, '%Y-%m-01') as Months from Table GROUP BY Months;

This:

Selects the date in the format you specified (i.e., as first day of month)
Groups by month so that you get one record returned for each month

answered Apr 13 '14 at 02:55

ff524

387
4
19

Sorry, this returns some wonky results. – Burning Hippo Apr 13 '14 at 02:57
2

explain "wonky results" – pennstatephil Apr 13 '14 at 02:58

score 2 · Answer 2 · edited May 23 '17 at 12:31

2

try this: select DATE_FORMAT(date, '%Y-%m-01') as Months from Table group by Months

based off this question: MySQL round date to the start of the week and month

and this doc: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

edited May 23 '17 at 12:31

Community

1
1

answered Apr 13 '14 at 02:48

pennstatephil

1,593
3
22
43

you didn't say that in the question ;) – pennstatephil Apr 13 '14 at 02:51
Sorry, thought the "what I want the list to look like" implied it. – Burning Hippo Apr 13 '14 at 02:52
better to be explicit-- at least 2 of us didn't get it – pennstatephil Apr 13 '14 at 02:52

score 1 · Answer 3 · answered Apr 13 '14 at 02:49

1

I think what you want is this:

SELECT date FROM tableName GROUP BY date;

answered Apr 13 '14 at 02:49

Outsider

550
3
9

score 1 · Answer 4 · answered Apr 13 '14 at 02:56

1

this will group your dates by month of each date

SELECT 
    DATE(date) as your_date 
FROM myTable 
GROUP BY MONTH(your_date);

to handle multiple years, you can also group by year..

GROUP BY MONTH(your_date), YEAR(your_date)

answered Apr 13 '14 at 02:56

John Ruddell

25,283
6
57
86

score 0 · Answer 5 · answered Apr 16 '14 at 02:12

0

SELECT DISTINCT DATE_FORMAT(date, '%Y-%m-01') FROM Table

answered Apr 16 '14 at 02:12

Ashraf Bashir

9,686
15
57
82

MySQL query to return a list of months

5 Answers5