I am redoing the question because I did not get the desired result, I would like to do a select in the date field but the between is not working, I believe it is due to my date field being in VARCHAR format, I tested the code below:
("SELECT * FROM caixa WHERE data BETWEEN '01/05/2017' AND '31/05/2017'");
However, he selected all the records, not just the ones that dated between 05/05/2017 and 05/31/2017.
The correct thing is to store dates in fields of type date , datetime or timestamp , but if you can not restructure your table, with STR_TO_DATE you can convert string to date . In your case it would look like this:
SELECT * FROM caixa WHERE STR_TO_DATE(data, '%d/%m/%Y') BETWEEN STR_TO_DATE('01/05/2017', '%d/%m/%Y') AND STR_TO_DATE('31/05/2017', '%d/%m/%Y');
To learn more about STR_TO_DATE click here .
SELECT
*
FROM
caixa
WHERE
DATE_FORMAT (
DATA,
'%Y-%m-%d %H:%i:%s') BETWEEN '2017-05-01 00:00:00' AND '2017-05-31 23:59:59'
Change the field type to a date type field. There are several as mentioned in the other answer.
SELECT * FROM caixa WHERE cast(data as date) BETWEEN '01/05/2017' AND '31/05/2017'