MySQL query problem. Last MONTH dates

I have a dateBill field -DATETIME- in my DB. I would like to retrieve all the info from THE LAST MONTH.

So, today is: 2011-08-02 12:00:00

My query is:

SELECT *开发者_JAVA百科
FROM bills
WHERE DATE(dateBill) > DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND status = 1
ORDER BY id_bill

The status just says if the bill is approved or not.

But I get some weid results:

 2011-09-01 21:44:07
 2012-08-01 00:00:00

I inserted those values just to test. As you can see, it is not working.

Any help, please?

DATE(dateBill) > DATE_SUB(CURDATE(), INTERVAL 1 MONTH) captures dates in the future too.

including next month

2011-09-01 21:44:07

and next year

2012-08-01 00:00:00

add AND CURDATE() <= DATE(dateBill) if you dont like that...

SELECT *
FROM bills
WHERE 
  YEAR(dateBill) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND
  MONTH(dateBill) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND 
  status = 1
ORDER BY id_bill