Mysql datetime between two columns

Question

I have two columns (both datetime) of startDate and endDate in the event table. I get the current day using the date() php function.

This leads to the example of 2013-03-12.

Now there are three event possibilities combined with the dates that occur today:

• The event begins and ends on this day.
• The event started earlier and ends today.
• The event starts today, but ends in the future (> = 2013-03-13)

Now I would like to break these all down into separate queries, since I'm not used to working with dates. I started with the first request, but I already failed on this. I tried the following:

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

and:

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

I also tried to use date() and format dates like "2013-03-12%".

I do not know why this does not work, although I am sure that there is at least one event that occurs on the 12th. Any help is appreciated.

Answer 1

Try using the DATE() function to crop date columns of only part of the date:

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

Answer 2

You can use the DATE() function, as other answers suggest, but I think it makes it harder to use an index on columns. Instead, you can include time 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' 

Answer 3

The DATETIME data type in MySQL also takes into account the time of day, so it will not match anything.

If you are not using the temporary part, you can simply reduce the data type to DATE instead of DATETIME . If not, you can use the DATE() function to get rid of the time part and consider only the date part

Answer 4

NOTE THIS WHEN USING between in Mysql

 date_column_name between 'startDate' AND 'endDate' 

NOTE: you should insert +1 date in endDate . Due to when you insert date 2015-05-18 into endDate . You cannot get data 2015-05-18. So you need plus one date before endDate .