CODEWITHSUNDEEP
mysqlsql
Burning Hippo
Burning Hippo

Reputation: 805

MySQL query to return a list of months

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?

Upvotes: 1

Views: 3241

Answers (5)

Ashraf Bashir
Ashraf Bashir

Reputation: 9804

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

Upvotes: 0

pennstatephil
pennstatephil

Reputation: 1643

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

Upvotes: 2

John Ruddell
John Ruddell

Reputation: 25842

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)

Upvotes: 1

ff524
ff524

Reputation: 387

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

Upvotes: 7

Outsider
Outsider

Reputation: 550

I think what you want is this:

SELECT date FROM tableName GROUP BY date;

Upvotes: 1

Related Questions