Take the 2-minute tour ×

how would I generate a MySQL query to grab a row that is between a particular month eg grab row where month is between 01/01 (1st Jan) and 01/06 (1st June)

share|improve this question
add comment

5 Answers

up vote 4 down vote accepted

I'm not sure, if you want to grab all rows from Jan to June, only the rows until June 1st or all rows from Jan to May. You can use for example:

[...] WHERE `date_column` BETWEEN '2012-01-01' AND '2012-06-01'

which gives you all rows including June 1st.

Use this to get all rows from the full months of Jan to June:

[...] WHERE YEAR(`date_column`)=2012 AND MONTH(`date_column`) BETWEEN 1 AND 6

(change to BETWEEN 1 AND 5 for Jan to May)

share|improve this answer
add comment

Assuming the column in mysql is the right format (date/datetime) you can simply compare (>,<) :

SELECT somecolumn 
FROM sometable
WHERE datecolumn >= '2012-01-01' AND datecolumn < '2012-06-01'

Hope it helps!

share|improve this answer
wow - awesome response guys! looks like all your methods should do the trick! thanks again –  Zabs Jan 19 '12 at 10:31
add comment
  date BETWEEN '2012-01-01' AND '2012-06-01';
share|improve this answer
add comment

If you generate your SQL query in PHP, you can use BETWEEN

$date = date("Y") . '-' . date("m") . '-01';
$sql = "SELECT * 
FROM `table`
WHERE `date_column` BETWEEN '$date' AND '$date' + INTERVAL 1 MONTH - INTERVAL 1 DAY";

(I subtracted one day to exclude the data of the next month first day)

Or you can use EXTRACT

$month = 5;
$sql = 'SELECT * 
FROM `table`
WHERE EXTRACT(MONTH FROM `date_column`) = ' . $month;
share|improve this answer
Your first idea isn't so hot because if you take 1 month off of January you end up at Month 0 which doesn't exist.. Instead using the make time commands in PHP would be better wouldn't it? –  Paul Nov 25 '12 at 13:25
@Paul, let me disagree: adding/subtracting intervals correctly handles such cases. In the example you suggest it will return previous year December. –  Minras Nov 26 '12 at 14:23
Ok worth pointing out –  Paul Nov 27 '12 at 11:53
add comment

You can use the month(), year() and day() functions. So your query would look like something like

SELECT * FROM table WHERE month(date_field) >= 1 AND month(date_field) <= 6 AND year(date_field) = 2011

date_field should be a date or datetime field, if it is a timestamp, you need to use the FROM_UNIXTIMESTAMP function so it would look like month(FROM_UNIXTIMESTAMP(timestamp_field))

share|improve this answer
add comment

Your Answer


