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)