Difference comparing dates in MariaDB and MySQL

I have two servers using the same app with similar data. The app is trying to launch a query to retrieve some records, using some dates as condition.

Let say we have this data:

ID start_date
1 2021-07-14
2 2021-07-15

And we try this query in each system.

System A – 10.1.48-MariaDB-0+deb9u2:

SELECT * FROM table WHERE start_date >= '2021-07-14 00:00:00' AND start_date < '2021-07-14 24:00:00'

It doesn’t return anything

System B – MySQL Community Server (GPL) 5.7.32:

SELECT * FROM table WHERE start_date >= '2021-07-14 00:00:00' AND start_date < '2021-07-14 24:00:00'

It returns:

ID start_date
1 2021-07-14

I have also tried removing the time “00:00:00” but same behaviour.

I suppose there would be a configuration Global Variable that I could change so it has the same result.

I would like NOT to change the query, if possible.

Any idea?

Thanks in advance

Answer

You should change the query. You are suggesting that ‘2021-07-14 24:00:00’ and ‘2021-07-15 00:00:00’ represent the same value. Try using this instead:

SELECT * FROM table WHERE start_date >= '2021-07-14 00:00:00' AND start_date <= '2021-07-14 23:59:59'

You could also reduce number of literals in your query:

SELECT * FROM table WHERE DATE(start_date) = '2021-07-14'