BETWEEN

expr BETWEEN min AND max is shorthand. If all three arguments are of the same type it is exactly (min <= expr AND expr <= max) — both bounds are included. NOT BETWEEN is NOT (expr BETWEEN min AND max). If expr is NULL the result is NULL, so the row is dropped by either form.

Why dates go wrong

Comparing a DATE with a DATETIME coerces the DATE to DATETIME by adding a time part of '00:00:00'. A string literal in a date comparison behaves the same way. So created_at BETWEEN '2026-01-01' AND '2026-01-31' against a DATETIME column really means up to and including midnight at the start of the 31st, and throws away almost the entire last day of the range.

Use a half-open range instead

Write created_at >= '2026-01-01' AND created_at < '2026-02-01'. The upper bound is exclusive, so it covers every instant of January no matter how many fractional-second digits the column has, and consecutive ranges tile without gaps or overlaps. It also stays index-friendly, because the column is still compared bare rather than wrapped in DATE().

BETWEEN is fine as written for a pure DATE column, for integers and for decimals — anywhere the endpoints are genuinely discrete values you want included.

-- events.occurred_at is DATETIME(6).

-- Off by one day. '2026-01-31' becomes '2026-01-31 00:00:00',
-- so everything after midnight on the 31st is missing.
SELECT id, occurred_at
FROM events
WHERE occurred_at BETWEEN '2026-01-01' AND '2026-01-31';

-- Still wrong: '23:59:59' misses the last second's fractional part,
-- e.g. '2026-01-31 23:59:59.500000'.
SELECT id, occurred_at
FROM events
WHERE occurred_at BETWEEN '2026-01-01' AND '2026-01-31 23:59:59';

-- Correct: half-open interval. Covers all of January, tiles cleanly
-- with the next month, and can still use an index on occurred_at.
SELECT id, occurred_at
FROM events
WHERE occurred_at >= '2026-01-01'
  AND occurred_at <  '2026-02-01';

-- Shorthand
SELECT * FROM products WHERE price_cents BETWEEN 1000 AND 5000;

-- Longhand: identical
SELECT * FROM products WHERE 1000 <= price_cents AND price_cents <= 5000;

-- Negated
SELECT * FROM products WHERE price_cents NOT BETWEEN 1000 AND 5000;
SELECT * FROM products WHERE NOT (price_cents BETWEEN 1000 AND 5000);

-- invoices.issued_on is DATE: BETWEEN is exactly right here,
-- because both endpoints are whole days.
SELECT id FROM invoices
WHERE issued_on BETWEEN '2026-01-01' AND '2026-01-31';

-- invoices.created_at is DATETIME: use a half-open range.
SELECT id FROM invoices
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

-- Avoid wrapping the column in a function to dodge the problem.
-- This is correct but cannot use an index on created_at:
SELECT id FROM invoices
WHERE DATE(created_at) BETWEEN '2026-01-01' AND '2026-01-31';

-- Last 7 whole days, ending at the start of today.
SELECT DATE(created_at) AS day, COUNT(*) AS signups
FROM users
WHERE created_at >= CURDATE() - INTERVAL 7 DAY
  AND created_at <  CURDATE()
GROUP BY day
ORDER BY day;