SQL: BETWEEN is inclusive at both ends, which breaks on timestamps

BETWEEN '2026-08-01' AND '2026-08-31' misses everything after midnight on the 31st, because the date literal becomes 00:00:00. A half-open range is the correct shape.

Code

-- misses 23 hours 59 minutes of the last day
SELECT * FROM orders
 WHERE created_at BETWEEN '2026-08-01' AND '2026-08-31';

-- correct, and index-friendly
SELECT * FROM orders
 WHERE created_at >= '2026-08-01'
   AND created_at <  '2026-09-01';

Output

BETWEEN     : 8,102 rows
half-open   : 8,415 rows      <- 313 orders on the 31st after midnight

-- The half-open form also works unchanged for DATE, TIMESTAMP and
-- TIMESTAMPTZ columns, and never needs a leap-year or month-length special case.

Run this yourself in the Online Java Compiler, spin up a live REST API in the API Sandbox, or practise with Java interview questions.

Published 2026-08-25