I have the following situation, where when trying to query a date until another date using BETWEEN , the specified date does not return, just "the previous day".
Is there any outline to resolve this issue?
Following the dates saved in the database and the generated Select:
2016-04-12 16:07:03
2016-04-12 16:33:08
2016-04-14 10:47:33
2016-04-14 11:21:14
SELECT * FROM sales.logger where data BETWEEN "2016-04-12" AND "2016-04-14" ORDER BY data;
Return :
2016-04-12 16:07:03
2016-04-12 16:33:08
The problem seems to be that you are treating DATETIME types as DATE .
Try:
SELECT * FROM sales.logger where data BETWEEN "2016-04-12 00:00:00" AND "2016-04-14 23:59:59" ORDER BY data;
A stable solution for selecting data between two dates is to not use the end date, but rather the day after the end date . When you have a two-date filter on your system, you get the next day from the longest date in the database:
SELECT * FROM sales.logger where data >= "2016-04-12" AND data < "2016-04-15";
Note that the lowest date is compared to a greater equal, but the end date is compared to only less.
This code works:
It seems like an ugly solution, but it generates code that always works, but mostly when you are using code generation tools, where there may be no control of what precision the date field is being used (date, timestamp, timestamp with fraction N ).
To solve the problem, there are two ways of doing BETWEEN , when using datetime :
SELECT * FROM sales.logger where DATE_FORMAT(data, '%Y-%m-%d')
BETWEEN '2016-04-12' AND '2016-04-14' ORDER BY data;
or
SELECT * FROM sales.logger where data
BETWEEN '2016-04-12 00:00:00' AND '2016-04-14 00:00:00' ORDER BY data;
You can convert the date
Try:
SELECT * FROM sales.logger where date(data) BETWEEN "14-04-2016" AND "14-04-2016" ORDER BY data;