CODEWITHSUNDEEP
mysqlcakephp-2.1
Tom
Tom

Reputation: 3847

How can I query for +/- 10 days TheDate in DB query

Okei,, so I am trying to retrieve some info from my database with a +/- 10 days setting.

Values:

Event.start_date = y-m-d
Event.end_date = y-m-d
My.date = y-m-d

$conditions["My.date >="] = date( 'Y-m-d');
$conditions["My.date -10 >="] = Event.start_date;
$conditions["My.date +10 >="] = Event.end_date;

Any suggestions how I can achive this?

Upvotes: 1

Views: 55

Answers (2)

Tom
Tom

Reputation: 3847

This is how :)

$conditions = array(
 "OR" => array(
array('My.date BETWEEN DATE_ADD(?, INTERVAL -10 DAY) AND ?' => array($data['Event']['start_date'],$data['Event']['end_date'])),
array('My.date BETWEEN ? AND DATE_ADD(?, INTERVAL 10 DAY)' => array($data['Event']['start_date'],$data['Event']['end_date']))
            )
);

Upvotes: 0

bonCodigo
bonCodigo

Reputation: 14361

Try this in your query

If you are checking against today's date:

WHERE startDate BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 10 DAY)

If you are checking against your own end date:

WHERE startDate BETWEEN endDate() AND DATE_ADD(endDate(), INTERVAL 10 DAY)

For going back - 10 days

WHERE startDate BETWEEN DATE_ADD(startDate(), INTERVAL -10 DAY) AND endDate()

Please adjust logic according to your needs.

Upvotes: 3

Related Questions