Unit 08.00: Half-open ranges and the BETWEEN midnight bug

Unit ID: SQL-M08-U01 - Estimated active time: 14-17 minutes Objective: write a date range that includes every row in the period, and prove it does.

The query that loses a day

Ask for all of June:

SELECT COUNT(*) FROM orders
WHERE placed_at BETWEEN '2026-06-01' AND '2026-06-30';
-- 967

Now the same question with a half-open range:

SELECT COUNT(*) FROM orders
WHERE placed_at >= '2026-06-01' AND placed_at < '2026-07-01';
-- 1000

33 orders vanished. Every order placed on 30 June - the entire last day of the month.

Why BETWEEN did that

BETWEEN a AND b is inclusive of both endpoints, so the upper bound is literally 2026-06-30, which as a timestamp means 2026-06-30 00:00:00. Anything later that day is outside the range.

Our earliest order is at 10:00 and the latest at 21:00:

SELECT MIN(placed_at), MAX(placed_at) FROM orders;
-- 2026-06-01 10:00:00 | 2026-06-30 21:00:00

No order sits exactly at midnight, so all 33 of the final day's orders were excluded:

SELECT COUNT(*) FROM orders
WHERE placed_at >= '2026-06-30' AND placed_at < '2026-07-01';
-- 33

967 + 33 = 1,000. The arithmetic confirms exactly what was lost.

The pattern that always works

-- fragment: the range pattern, with placeholders
WHERE placed_at >= '<start of period>'
  AND placed_at <  '<start of NEXT period>'

Include the lower bound, exclude the upper. It is correct for dates and timestamps, at any precision, in every engine. You never have to reason about whether the column has a time component.

Why "just use 23:59:59" is not a fix

-- fragment: WHERE clause shown on its own
WHERE placed_at BETWEEN '2026-06-01' AND '2026-06-30 23:59:59'

This drops anything between 23:59:59.001 and midnight. With second precision you will probably never notice; with millisecond or microsecond timestamps you will, occasionally, and only in the busiest hour. The half-open range has no such edge.

Practice

Write a query counting orders in the second half of June, and state the two boundary values you used and why.

SELECT COUNT(*) FROM orders
WHERE placed_at >= '2026-06-16' AND placed_at < '2026-07-01';
-- 495

Takeaway

Use >= start AND < next_start. BETWEEN on a timestamp column silently truncates the final day to a single instant.

---

Mark complete