MySQL – DATE_ADD month interval

DATE_ADD works just fine with different months. The problem is that you are adding six months to 2001-01-01 and July 1st is supposed to be there.

This is what you want to do:

SELECT * 
FROM mydb 
WHERE creationdate BETWEEN "2011-01-01" 
                   AND DATE_ADD("2011-01-01", INTERVAL 6 MONTH) - INTERVAL 1 DAY
GROUP BY MONTH(creationdate)

OR

SELECT * 
FROM mydb 
WHERE creationdate >= "2011-01-01" 
AND creationdate < DATE_ADD("2011-01-01", INTERVAL 6 MONTH)
GROUP BY MONTH(creationdate)

For further learning, take a look at DATE_ADD documentation.

*edited to correct syntax