Select records from one week previous in mysql

I have data that has a date (Y-m-d H:i:s) column (the type is datetime). I'd like to select all records from 1 week before a date I enter (in the below example: 2011-09-17 00:00:00, but am having some trouble. Here's what I've got:

SELECT * FROM em开发者_开发知识库ails WHERE (DATE(date) = date_sub(date('2011-09-17 00:00:00'), 1 week))

What am I doing wrong? Thanks

---

I think you're missing INTERVAL at the front of 1 week:

SELECT *
FROM emails
WHERE (DATE(date) = date_sub(date('2011-09-17 00:00:00'), INTERVAL 1 week));

Here is a query that I ran that does work for the DATE_SUB() part:

SELECT *
FROM wp_posts
WHERE post_modified > DATE_SUB(CURDATE(), INTERVAL 4 WEEK);

You can use a negative value to go do a "N weeks before given date" query so something like this would work:

SELECT *
FROM wp_posts
WHERE post_modified > DATE_SUB(CURDATE(), INTERVAL -1 WEEK);

Or:

SELECT *
FROM emails
WHERE (DATE(date) = date_sub(date('2011-09-17 00:00:00'), INTERVAL -1 week))

---

Try this, I like to stick with DATE_ADD and just use a negative value.

SELECT * FROM emails WHERE date >= DATE_ADD('2011-09-17 00:00:00', INTERVAL -1 WEEK)

---

SELECT * FROM wp_posts
WHERE post_modified  
BETWEEN SYSDATE() - INTERVAL 7 DAY
AND SYSDATE();