mysql return rows matching year month

Question

How would I go about doing a query that returns results of all rows that contain dates for current year and month at the time of query.

Timestamps for each row are formated as such: yyyy-mm-dd

I know it probably has something to do with the date function and that I must somehow set a special parameter to make it spit out like such: yyyy-mm-%%.

Setting days to be wild card character would do the trick but I can't seem to figure it out how to do it.

Here is a link to for quick reference to date-time functions in mysql:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Thanks

score 13 · Accepted Answer · answered Sep 14 '09 at 16:58

13

I think EXTRACT is the function you are looking for:

SELECT * FROM table
WHERE EXTRACT(YEAR_MONTH FROM timestamp_field) = EXTRACT(YEAR_MONTH FROM NOW())

answered Sep 14 '09 at 16:58

Eric Petroelje

59,820
9
127
177

Ahh, you read my mind. This is exactly what I needed! Thanks Eric. – payling Sep 14 '09 at 17:04

score 5 · Answer 2 · answered Sep 14 '09 at 17:02

5

you could extract the year and month using a function, but that will not be able to use an index.

if you want scalable performance, you need to do this:

SELECT *
  FROM myTable
 WHERE some_date_column BETWEEN '2009-01-01' AND '2009-01-31'

answered Sep 14 '09 at 17:02

longneck

11,938
2
36
44

+1 on this - the solution I gave solves the problem, but will likely NOT scale well if you have lots of data. – Eric Petroelje Sep 14 '09 at 17:27
This answer is still helping! – The Humble Rat Jan 19 '15 at 16:36

score 1 · Answer 3 · edited May 09 '16 at 11:13

1

select * from someTable where year(myDt) = 2009 and month(myDt) = 9 and day(myDt) = 12

edited May 09 '16 at 11:13

Deenadhayalan Manoharan

5,436
14
30
50

answered Sep 14 '09 at 17:03

Alexey Sviridov

3,360
28
33

mysql return rows matching year month

3 Answers3

Linked