18

I want to select rows from the datetime now till 7 days in the future, how can I do this? Read alot about the date function of mysql but cant figure it out, this is the MySQL code:

SELECT  id, date_format(datum, '%d/%m') AS date,
        date_format(datum, '%H:%i') AS time, date  
FROM wedstrijden
WHERE date >= now()
ORDER BY datum asc

I have to do something with:

date >= now() till 7 days further
RbG
  • 3,181
  • 3
  • 32
  • 43
Frank
  • 499
  • 2
  • 10
  • 22

5 Answers5

62

I would submit that the most elegant way would be:

WHERE `date` BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY)

Edit: this doc page is like the most useful thing ever. Bookmark it, because it is totally handy.

TehShrike
  • 9,855
  • 2
  • 33
  • 28
7

You could use the INTERVAL modifier to add a week to the current time as follows:

...WHERE date >= NOW() AND date <= NOW() + INTERVAL 7 DAY;
John Parker
  • 54,048
  • 11
  • 129
  • 129
  • This is the right answer with Tehshrike method I receive mysql_error because it is not BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAYS) but BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY) days =day – Frank Jan 26 '11 at 16:49
2

I am also posting the query which worked for me

where matchdate BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 7 DAY) This worked if above example doesn't work then try this.

Muhammad Usman
  • 90
  • 7
1

What i use to get all the data from 7 days back till now from the database:

SELECT * FROM wedstrijden WHERE DATE(date_from_table) > CURDATE() + INTERVAL 7 DAY

Kima
  • 119
  • 1
  • 4
0

Something like:

"...WHERE date >= NOW() AND date <= ADDTIME(NOW(), 168:00:00)..."

should accomplish what you're looking for. The 168:00:00 is a bit specific for your needs, ADDTIME takes any datetime format.

markf
  • 127
  • 7