Query Between MySQL does not return correct!

Question

Query Between MySQL does not return correct!

Navigation

#1 by (2 votes)
#2 by (2 votes)

1

I have a simple query in a Datetime field, I have to query two dates, of course retrieving 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 this way, the data are not displayed today, they only show yesterday's values for less than 3 days, that is, my accounts registered on 7/21 are not displayed, and today it is 7/21.

The result is returning as if it had done this:

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

What's wrong?

mysql

asked by anonymous 21.07.2016 / 13:55

2 answers

2

Use the DATE () function for convert your DateTime to a Date, so you will not 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())

21.07.2016 / 14:19

How to delete any row from a data frame in R? [duplicate] 2 DataGridView when clicking on the first play record in 2?

score 2 · Accepted Answer

You can use INTERVAL to make 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();