I am running into an issue that only occurs on our MySQL 5.1 systems. It works fine on MySql 5.6.
I have a table that has two DateTime columns ("start" and "end"). I want to select all rows where a date is between these two columns or the same.
Basically this: row.start <= MY_DATE_TIME <= row.end
But when I execute this...
select * from alerts a
where a.start <= "2019-11-08 00:00:00"
and a.end >= "2019-11-08 00:00:00"
... I also get one row where the a.start is "2019-10-10 07:40:00" and a.end is "2019-11-20 23:55:00". My given date is clearly outside that range, so what am I doing wrong?
I also tried it like this with the same result:
select * from alerts a
where STR_TO_DATE("2019-11-08 00:00:00", '%Y-%m-%D %T') between a.start and a.end
Any ideas?