Mysql datetime between two columns

Question

I've got two columns (both datetime) startDate and endDate in an events table. I am retrieving the current day using the the date() function of php.

This results in for example 2013-03-12.

Now there are three possibilities of events combined with dates that occur today:

1. An event starts and also end on this day
2. An event has started earlier and ends today
3. An event starts today but ends in the future (>= 2013-03-13)

Now I'd like to break these all into separate queries as I'm not used to work with dates. I started with the first query, but I am already failing on that one. I've tried the following:

SELECT * FROM events WHERE (startDate= '2013-03-12' AND endDate= '2013-03-12')

aswell as:

SELECT * FROM events WHERE NOT (startDate < '2013-03-12' OR endDate > '2013-03-12')

I've tried to use DATE() aswell and to format dates like '2013-03-12%'.

I don't know why it doesn't work while i am sure there is at least 1 event that is taking place on the 12th. Any help is appreciated.

Answer 1

Try using the MySQL's DATE() function to trim the date columns to the just the date parts:

SELECT * 
FROM events 
WHERE (DATE(startDate) = '2013-03-12' AND DATE(endDate)= '2013-03-12')