CODEWITHSUNDEEP
mysqlsqldate
user460114
user460114

Reputation: 1871

MySQL Query does not return current day's records

The following query does not return records for the ending date (20th April). I read somewhere that is it because time is not taken into account with the date passed into the query. But after researching various solutions, I can't seem to figure it out for MySQL.

select * from listings
where created_date >= '2020-04-18' 
and created_date <= '2020-04-20'

created_date is a datetime field.

Note, I can't use "between" in this situation.

Upvotes: 0

Views: 174

Answers (2)

Nick
Nick

Reputation: 147266

Because created_date is a DATETIME, it will have a time part which is probably not zero. When you compare it to a date with no time part, the time part of that date is set to 0, and so the test fails. You need to preferably use:

 created_date < '2020-04-21'

or you can include the time part, but this will slow comparisons:

 created_date <= '2020-04-20 23:59:59'

or alternatively, take the DATE part and compare that, but that has the downside of preventing use of an index on created_date:

DATE(created_date) <= '2020-04-20'

Demo on SQLFiddle

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The best logic is:

where created_date >= '2020-04-18' and
      created_date < '2020-04-21'

Or, if you prefer:

where created_date >= '2020-04-18' and
      created_date < date('2020-04-20') + interval 1 day

These are index- and optimizer- safe because there are no functions on the column. They also work regardless of whether or not there is a time component.

Upvotes: 0

Related Questions