payling payling - 6 months ago 32
SQL Question

mysql return rows matching year month

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

Answer

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