How to get records based on todays date?

[dalton]

Hi, I have an events database that has two datetime field types for example:

2001-03-22 01:01:00

One for the beginning of each event and one for the ending of each event.

I want to create a query that returns all events whos ending date has not yet past and order them from the most recent beginning date to the most future beginning date.

I have the following query so far

$query = "SELECT DATE_FORMAT(startdate, '%M %D, %Y %l:%i %p') AS for_startdate,DATE_FORMAT(enddate, '%M %D, %Y %l:%i %p') AS for_enddate,event,description,cost,url FROM tablename WHERE "something here to restrict to ending dates >= today" ORDER BY "something here to sort by beginning date with most recent ones first";

I think this might be easier if I was not using DATE_FORMAT in the query but is it possible to get the result I want and still use it? Appreciate any help! Thanks.

[dalton]

Hi I got them to sort by startdate by adding ORDER BY startdate ASC - did not seem to work last time but is working now. Now I just need to know how to restrict the query to records with ending dates greater than or equal to todays date. I added the following before the query to get todays date in the same format that the datetime values are stored in the database,

$today = date("Y-m-d 00:00:00");

but when I add a

WHERE enddate >= $today

into the query I get a error. Appreciate any ideas. Thanks.

[dalton]

Wow, don't I feel stupid - I forgot to surround $today in single quotes! Seems to be working fine now. 🙂

[[deleted]]

If you are using MySQL, the easiest way to get "today" is to use the mysql function called "now()" in your query.

SELECT * FROM table WHERE date_field<now();

Note: now() is the current date and time, so it is not just "today" it is "right now"

[dalton]

Thanks vincent, appreciate the info about using the now() function.