CODEWITHSUNDEEP
mysqlsql
numerical25
numerical25

Reputation: 10790

finding records that have dates that fall before the date 7 days from now. using datetime format

I am trying to find records based on the current date. One of the queries I am trying to do is find any record that falls within 7 days of the current date. this is what I have

WHERE `Project`.`delivery_deadline` <= 'DATE_SUB(CURDATE(),INTERVAL 7 DAY)' 
GROUP BY `Project`.`id` 
LIMIT 20

This isn't working of course. what exactly am I doing wrong. delivery_deadline is datetime format not date format

Upvotes: 0

Views: 641

Answers (3)

user1092803
user1092803

Reputation: 3277

Should not you write: 

WHERE `Project`.`delivery_deadline` >= 'DATE_SUB(CURDATE(),INTERVAL 7 DAY)'

edit

The correct solution is:

WHERE Project.delivery_deadline between now() and date_add(now() ,interval 7 day)

Upvotes: 1

Runar J&#248;rgensen
Runar J&#248;rgensen

Reputation: 544

You can edit your query to look like this. That'll give you your desired result:

WHERE `Project`.`delivery_deadline` >= CURDATE() 
AND `Project`.`delivery_deadline` <= (CURDATE() + 7)
GROUP BY `Project`.`id` 
LIMIT 20

A project that ends earliest today or no later than 7 days from today.

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65264

WHERE `Project`.`delivery_deadline` <= 'DATE_SUB(CURDATE(),INTERVAL 7 DAY)' 
GROUP BY `Project`.`id` 
LIMIT 20

compares your deadline against the string 'DATE_SUB(CURDATE(),INTERVAL 7 DAY)', whereas

WHERE `Project`.`delivery_deadline` <= DATE_SUB(CURDATE(),INTERVAL 7 DAY) 
GROUP BY `Project`.`id` 
LIMIT 20

compares it against the result of DATE_SUB(), which could be what you want.

Upvotes: 4

Related Questions