mysql date functions

Question

I have a date field in the database table of this format 2012-02-1.i need to write 3 different queries:

a.) I need to retrieve all fields where date is between today and previous 5 days.
b.) I need to retrieve all fields where date is older than 5 days from today's date.
c.) I need to retrieve all fields where date between '5 days ago' to '30 days ago'

Can I use some inbuilt mysql function.

Manipulating the query below:

SELECT fields 
FROM table 
WHERE date >= CURDATE() - 5

or something like this

Or using a between clause. I am not getting the syntax correct.

SELECT p.status,p.downpayment_date,p.policy_id,i.id,i.policy_type,i.carrier,i.policy_nu‌​mber,i.client_id,c.id,c.client_name FROM pdp_payment AS p,pdp_policy_info AS i,pdp_client_info AS c WHERE p.policy_id=i.id AND i.client_id=c.id AND (((p.status='close pending') OR (p.status='Cancel')) AND (p.downpayment_date BETWEEN ((INTERVAL 5 DAY AND CURDATE()) - (INTERVAL 30 DAY AND CURDATE()))) )

Answer 1

Date between today and previous 5 days.

SELECT  fields FROM table
WHERE date_field BETWEEN CURRENT_DATE - INTERVAL 5 DAY AND CURRENT_DATE

Date smaller than previous 5 days.

SELECT  fields FROM table
WHERE date_field < CURRENT_DATE - INTERVAL 5 DAY

Answer 2

For all fields where date is between today and previous 5 days.

SELECT  fields
FROM table
WHERE your_date_field_name BETWEEN CURDATE() - INTERVAL 5 DAY AND CURDATE()

You can work out other problems in a similar way

Answer 3

date is a keyword, so when you use it as a field name it MUST be enclosed in backticks ` otherwise you will get a parse error.

To get the range you want:

WHERE `date` BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY) AND DATE_ADD(NOW(),INTERVAL -5 DAY)