SELECT statement not returning values with BETWEEN clause containing dates
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
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.