Gotcha: SQL BETWEEN is inclusive on both ends

BETWEEN includes both endpoints, which can cause off-by-one errors, especially with dates and timestamps.

BETWEEN is inclusive: a BETWEEN x AND y means a >= x AND a <= y

-- Date gotcha:
SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';
-- MISSES orders on Jan 31 after midnight!
-- '2026-01-31' is '2026-01-31 00:00:00'
-- Order at '2026-01-31 15:30:00' is NOT included!

-- Fix: use explicit comparison:
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2026-02-01'; -- Exclusive upper bound

-- Integer gotcha:
SELECT * FROM t WHERE id BETWEEN 1 AND 10;
-- Returns 10 rows (1,2,3,...,10), not 9

-- Empty range:
SELECT * FROM t WHERE id BETWEEN 10 AND 1;
-- Returns NOTHING (not reversed, just empty)

-- NOT BETWEEN:
SELECT * FROM t WHERE id NOT BETWEEN 5 AND 10;
-- Returns id < 5 OR id > 10

Best practice:

• For dates/timestamps: use >= and < (half-open interval)
• For integers: BETWEEN is fine if you want inclusive
• Always test edge cases at boundaries