0

I need to list and records in a table where "theDate" field is either current or future.

The "theDate" field will contain a date in this format: Year-month-day ... so something like: 2014-12-31

So I have SELECT * FROM events but I need the WHERE part so I can get only current and future dates.

How can I do this?

Satch3000
  • 47,356
  • 86
  • 216
  • 346
  • The answer is going to depend on whether MySQL and PHP are running within the same timezone and whether timezone is indeed a factor. – Ja͢ck Nov 25 '14 at 14:13
  • Also, are you storing the date inside a DATE column or is it a (VAR)CHAR field? – Ja͢ck Nov 25 '14 at 14:20
  • [If you want your query to be cacheable](https://stackoverflow.com/questions/4726913/mysql-cache-and-date-functions), you should not use the `NOW()` or `CURDATE()` functions, but should send the actual date (using PHP's `date()` function) in your query. – Jo. Sep 28 '17 at 16:46

2 Answers2

3

You can use a MySQL keyword called NOW() and a simple greater than or equal to test

WHERE theDate >= NOW()
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
  • Not if like he said the theDate field only contains a data and not a time component. – RiggsFolly Nov 25 '14 at 14:17
  • Are you saying that MySQL compares DATE<->DATETIME as DATE<->DATE? Almost certain you should use `DATE(NOW())` to make a sane comparison (assuming today lasts the whole day). – Ja͢ck Nov 25 '14 at 14:22
  • You had me worried, just tested it and it does as I expected on `date` columns but not on `datetime` columns as you suspected. – RiggsFolly Nov 25 '14 at 14:31
  • Thanks for testing that; I would still argue that it's somewhat sloppy ;-) – Ja͢ck Nov 25 '14 at 14:38
3
SELECT * FROM events
WHERE date_column >= CURDATE();

date_column being the name of your column that contains the date.

CURDATE() returns the current date.

CrazyDog
  • 321
  • 3
  • 17