MySQL between not gathering last date

Question

Table

+--+------------+ |Id| Date | +--+------------+ |1 | 2017-08-08 | +--+------------+ |2 | 2017-08-23 | +--+------------+ |3 | 2017-08-23 | +--+------------+ |4 | 2017-08-24 | +--+------------+ |5 | 2017-08-24 | +--+------------+

Query

SELECT * FROM BoxExits WHERE Date BETWEEN '2017-08-08' AND '2017-08-24'

Problem

When i execute the query i receive the expected dated (from id 1 to 3) except for all rows that contains this date 2017-08-24 (from id 4 to 5) the only way to gather it is by adding one day so that the query looks like this

SELECT * FROM BoxExits WHERE Date BETWEEN '2017-08-08' AND '2017-08-25'

In this case is will retrieve all rows that contains this date 2017-08-24 (from id 4 to 5), since the user is the one that will chose the dates i don't want to force him to add one day to the date is looking for.

So how can i do it to the query give me also the max date (2017-08-24)?

My Research

While Searching i didn't find much except for this:

SELECT * FROM BoxExits WHERE Date >= '2017-08-08' AND Date <= '2017-08-24'

since the query says Date <= '2017-08-24' 2017-08-24 should be included but the problem maintains.

Environment Test I Tested it in phpmyadmin and a c# program that i am creating and both are giving me the same result (rows from 1 to 3).

I think you should need betwee like @scaisedge mentioned in his answer. In MySQL we use between for matching dates — Noob
– Noob, Commented Aug 25, 2017 at 10:54
@pekira You have some datetime like 2017-08-24 12:00:00 which is greater than 2017-08-24 00:00:00 which is what the raw date becomes in the comparison. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 25, 2017 at 10:59

Tim Biegeleisen · Accepted Answer · 2017-08-25 10:54:44Z

10

It could be that you have a datetime for Date so try using only the date part:

SELECT * FROM BoxExits WHERE DATE(Date) BETWEEN '2017-08-08' AND '2017-08-24'

edited Aug 25, 2017 at 10:54

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

answered Aug 25, 2017 at 10:52

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Steve Over a year ago

But what if there is an index of the Date column? Will this cause a missed index by the engine?

ScaisEdge Over a year ago

the question by OP if for not get the expected result for a between .. on date .. the reason is that the OP compare a datetime with a date value so the result don't match as expected .. for the index ond the date column ..normally a function on a column can invalid the use of index ..but for this the OP can also create and index for DATE(date)

Strawberry Over a year ago

If this were true then the result would show a time component !?!? And, remembering that this is MySQL, I'm not sure what's meant by creating an 'index for DATE(date)'

ScaisEdge Over a year ago

in mysql 5.7.5 is possible using generated column and before .. if the performance are so important is possibile manage this istuation using a trigger for build the proper column values ..

K.Dᴀᴠɪs · Accepted Answer · 2018-03-22 07:29:40Z

it is correct format
use date() function which get date only from datetime. Range of result include both date

SELECT * FROM BoxExits WHERE date(Date) BETWEEN '2017-08-08' AND '2017-08-24'

Collectives™ on Stack Overflow

MySQL between not gathering last date

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related