The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

SELECT statement not returning values with BETWEEN clause containing dates

Hi

MariaDB 10.4 PHPmyAdmin 5.0.2 PHP 7.4.5

I have a DB with 10 entries with dates ranging from 2020-05-14 to 2020-06-08. I want to search for records ranging from today's date backing up a certain number of days (x)

I have the following query: SELECT idField, date1Field, date2Field FROM db WHERE idField =1 AND (date1Field BETWEEN (CURRENT_DATE() - x) AND CURRENT_DATE()) ORDER BY date1Field DESC;

The results are consistent except for x=77 to x=94 where I do get only the 2 or 3 most recents records instead of all 10 as from x=25

Any thoughts

Answer Answered by Jan Steinman in this comment.

CURRENT_DATE() returns an INTeger.

Try using ADDDATE(CURRENT_DATE, -x).

Or CONVERT(CURRENT_DATE() -x, DATE)

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.