MySQL BETWEEN operator not working for DATETIME column

Question

I don't understand why the between operator isn't working in the following example:

CREATE TABLE orders (
    packed DATETIME
);

INSERT INTO orders VALUES ('2018-02-24 00:00:00');

SELECT COUNT(*) FROM orders WHERE packed = '2018-02-24 00:00:00';
SELECT COUNT(*) FROM orders WHERE (packed BETWEEN '2018-02-00 00:00:00' AND '2018-03-00 00:00:00');

The first select query returns 1. The second query returns 0. Why?

MySQL version: 14.14 Distrib 5.7.21

The 0th of february is not a valid date (and invalid dates will be replaced by 0). Replace it by the 1st. — Solarflare
– Solarflare, Commented Feb 25, 2018 at 11:09
@Solarflare Thank you very much. You should make it an answer. — Martin Heralecký
– Martin Heralecký, Commented Feb 25, 2018 at 11:20

Lukasz Szozda · Accepted Answer · 2018-02-25 11:55:24Z

2

It depends on MySQL sql_mode (STRICT_MODE):

set sql_mode=NO_ZERO_IN_DATE;

DROP TABLE orders;
CREATE TABLE orders (
    packed DATETIME
);

INSERT INTO orders VALUES ('2018-02-24 00:00:00');

SELECT COUNT(*) FROM orders WHERE packed = '2018-02-24 00:00:00';
-- 1
SELECT COUNT(*) FROM orders 
WHERE (packed BETWEEN '2018-02-00 00:00:00' AND '2018-03-00 00:00:00');
--0

DBFiddle Demo

But:

SELECT @@sql_mode; 
-- STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


CREATE TABLE orders (
    packed DATETIME
);

INSERT INTO orders VALUES ('2018-02-24 00:00:00');

SELECT COUNT(*) FROM orders WHERE packed = '2018-02-24 00:00:00';
-- 1
SELECT COUNT(*) FROM orders 
WHERE (packed BETWEEN '2018-02-00 00:00:00' AND '2018-03-00 00:00:00');
-- 1

DBFiddle Demo2

The correct way is to always provide valid dates.

edited Feb 25, 2018 at 11:55

answered Feb 25, 2018 at 11:49

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Collectives™ on Stack Overflow

MySQL BETWEEN operator not working for DATETIME column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related