displaying data between date ranges mysql

Question

Hi Guys,

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

Example date:

07/06/2010 00:00:00

needs 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

Answer 1

DATE_SUB(mydate, INTERVAL 6 MONTH)

Answer 2

Try this:

SELECT DATE_FORMAT(myDate - INTERVAL 6 MONTH, '%d/%m/%Y %H:%i:%s')

That would return:

07/12/2009 00:00:00

Answer 3

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

Answer 4

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.