Query datetime datatype with date only

[JBottero]

I'm using the following query:
SELECT * FROM IT_event_cal WHERE '$date' BETWEEN start_date AND stop_date

Where start and stop_date are of type date/time (0000-00-00 00:00:00).

For this particular query, I'd like to match a particular date (0000-00-00), and any time. How can I make the time part a wild-card value?

I use these columns in other parts of the application where I need the time. While I could break the times out into new columns, it seems more "tidy" to use the datetime data type.

Example:

Assume a record following values exists:

start_date: 2008-07-10 01:00:00
stop_date: 2008-07-10 03:00:00

then,

SELECT * FROM IT_event_cal WHERE '2008-07-10' BETWEEN start_date AND stop_date

returns an empty set. In other words no values. I want a result set that includes every record where the date part is (in this example) '2008-07-10'

[GarethMc]

Really depends which you find more elegant or not but you can either alter the $date and add a dummy time or just use the DATE() function extract only the date from the timestamp fields...



SELECT * FROM IT_event_cal WHERE '$date' BETWEEN DATE(start_date) AND DATE(stop_date)


OR



SELECT * FROM IT_event_cal WHERE '$date 12:00:00' BETWEEN start_date AND stop_date

:D