Query Between Mysql does not return correct!

Question

I have a simple consultation in a field Datetime, I must make the consultation stating two dates, of course recovering the values between them.

select 
  financ_conta_id,
  financ_conta_cadastro
from
  financ_conta 
where
  financ_conta_cadastro between CURRENT_DATE()-3 and CURRENT_DATE()

If I keep like this, the registration data today are not displayed, show only yesterday’s values less 3 days, that is, my registered accounts 21/07 are not displayed, and today is 21/07.

The result is returning as if it had done so:

 financ_conta_cadastro between CURRENT_DATE()-3 and '2016-07-22'

What’s wrong with it?

Answer 1

Use the DATE() Function to convert your Datetime to a Date, so you won’t count the hours of the day.

select 
  financ_conta_id,
  financ_conta_cadastro
from
  financ_conta 
where
  DATE(financ_conta_cadastro) between DATE(CURRENT_DATE()-3) and DATE(CURRENT_DATE())

Answer 2

You can use INTERVAL to take the difference of a date by days, month, year;

SELECT financ_conta_id, financ_conta_cadastro FROM financ_conta 
WHERE financ_conta_cadastro BETWEEN (NOW() - INTERVAL 3 DAY) AND NOW();