Finding records where todays date and time is between two datetime fields

Hi I have a table of competitions in MySQL. I have one datetime field for start_time and one datetime field for end_time.

What I would like to do is find the record (todays competition)and display it. This is done by searching the table for the record where todays date and time is later than start_time but sooner than end_time.

I have som trouble both getting todays time in the correct MySQL format as well as getting the query for find() or findByAttributes() right. Has anyone done this and is able to point me in right direction?

Raw SQL:


i’ve had to do a similar thing and solved with DAO:

SELECT * FROM my_table WHERE NOW() BETWEEN start_date AND finish_date

hope this helps

works like a charm for me



NOTE: merged duplicate posts

Thank you for the responses. Works like a charm now!