displaying data between date ranges mysql

I need to work out a period of 6 months (backwards) from a given date.

Example date:

07/06/2010 00:00:00

n开发者_运维百科eeds to count back 6 months and display:

07/12/2009 00:00:00

I have been scanning through: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html and non of these functions make sense to me :(

Hope this makes sense and any help would be appriciated.

Kyle

It's right at the top for the manual, surprised you couldn't find it (adddate / date_add);

mysql> SELECT DATE_ADD('2010-04-02 23:23:23', INTERVAL -6 MONTH);
+----------------------------------------------------+
| DATE_ADD('2010-04-02 23:23:23', INTERVAL -6 MONTH) |
+----------------------------------------------------+
| 2009-10-02 23:23:23                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

Possibly add a DATE_FORMAT() if you need it

DATE_SUB(mydate, INTERVAL 6 MONTH)

For the current date you can use

SELECT * FROM table 
WHERE mydate BETWEEN DATE_SUB(curdate(), INTERVAL 6 MONTH) and curdate();

in case of given date is not the current one, it should be 2010-06-07, not 07/06/2010.