Mysql DateTime compare

Hi I have a table of the following 2 records:

descript | start                | end
test 1   | 2011-07-18 14:30:00  | 2011-07-18 17:00:00
test 2   | 2011-07-18 00:00:00  | 2011-07-19 00:00:00

When I tried to do a select, I can't seems开发者_开发百科 to retrieve the 2nd result (test 2) which seems like clearly it is dated 19th of July.

SELECT * FROM event WHERE start >= "2011-07-18 00:00:00" AND end <= "2011-07-18 23:59:59";

Would appreciate any advise.

"2011-07-19 00:00:00" is MORE than "2011-07-18 23:59:59"

By your condition it should be less, so your query does not match test 2.

Your SQL query should be:

SELECT * FROM event 
WHERE start >= "2011-07-18 00:00:00" 
AND end <= "2011-07-19 00:00:00"; 

You can just do this :)

SELECT * FROM event
WHERE start BETWEEN '2011-07-18 00:00:00' AND '2011-07-19 00:00:00' 
AND end BETWEEN '2011-07-18 00:00:00' AND '2011-07-19 00:00:00'

This results in the times in between the range you specified for start AND end

you just need to exclude column end, like

SELECT * FROM event 
WHERE start>="2011-07-18 00:00:00" AND start<="2011-07-18 23:59:59";