mysql DATETIME BETWEEN query not working as expected

Question

I have following data which clearly showing data available between date used in query

My query

SELECT * FROM dashboard WHERE added BETWEEN "2018-05-08 10:32:32" AND "2018-05-08 10:28:30"

but This query returning empty row set. What going wrong. I have exported this table from production server & imported in development server. addedfield is DATETIME. Please help

Some manual reading would be useful expr BETWEEN min AND max (dev.mysql.com/doc/refman/8.0/en/…) — P.Salmon
– P.Salmon, Commented May 8, 2018 at 8:01

Paul Maxwell · Accepted Answer · 2018-05-08 07:54:46Z

3

The first boundary must be lower than the second.

Try reversing the comparison values. E.g

SELECT * FROM dashboard 
WHERE added BETWEEN "2018-05-08 10:28:30" 
      AND "2018-05-08 10:32:32"

The reason behind this is due to the fact that the first value is compared using >= then the second value compared using <= thus the second value must be greater than the first or nothing is returned.

edited May 8, 2018 at 7:54

answered May 8, 2018 at 7:49

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

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

Comments

apomene · Accepted Answer · 2018-05-08 07:49:44Z

1

the clause: BETWEEN "2018-05-08 10:32:32" AND "2018-05-08 10:28:30" is correctly returning an empty set since from is after until. Did you mean:

BETWEEN   "2018-05-08 10:28:30" AND "2018-05-08 10:32:32"

answered May 8, 2018 at 7:49

apomene

14.4k10 gold badges51 silver badges76 bronze badges

Collectives™ on Stack Overflow

mysql DATETIME BETWEEN query not working as expected

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related