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.

Solution 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')

Solution 2

You can use the DATE() function as other answers suggested, but I think this makes it hard to use an index on the columns. Instead, you can include times in your comparisons:

SELECT *
FROM events
WHERE startDate BETWEEN '2013-03-12 00:00:00' AND '2013-03-12 23:59:59'
  AND endDate BETWEEN '2013-03-12 00:00:00' AND '2013-03-12 23:59:59'

Solution 3

The DATETIME datatype in MySQL considers the time of the day as well, so, it will not match anything.

If you don't have any use of the time part, then you can simply reduce the datatype to DATE instead of DATETIME. If not, you can use the DATE() function to get rid of the time part and only consider the date part