Filter A Date Range With A Half-Open Boundary

Use an inclusive start and exclusive end to match a whole date range without missing rows that include times.

Created May 6, 2026 Last updated May 6, 2026 5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Return every order created during May

Orders 1 and 2 are inside May. Order 3 is exactly at the exclusive upper boundary, so it is excluded, and order 4 is before the inclusive lower boundary. This avoids depending on a final-day time such as 23:59:59.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE orders (id INT, created_at TIMESTAMP);

INSERT INTO
  orders (id, created_at)
VALUES
  (1, '2026-05-01 00:00:00'),
  (2, '2026-05-31 23:59:59'),
  (3, '2026-06-01 00:00:00'),
  (4, '2026-04-30 23:59:59');

Validated query Shared across supported engines.

SQL

SELECT
  id
FROM
  orders
WHERE
  created_at >= '2026-05-01'
  AND created_at < '2026-06-01'
ORDER BY
  id;

Expected result Returned rows for the shared example.

id
1
2

The same boundary logic works across supported engines. SQLite compares ISO timestamp text correctly in this example.

Useful when

Where this command helps.

returning all rows for a calendar month from a timestamp column
avoiding off-by-one date filters when rows store hours, minutes, or seconds

Explanation

What the command is doing.

Date and timestamp columns often include a time-of-day value. Filtering with BETWEEN can accidentally miss rows late on the final day if the upper bound is written as midnight. The safer reporting pattern is created_at >= start_date AND created_at < next_period_start, which includes every instant in the target period and excludes the first instant after it.

Filter A Date Range With A Half-Open Boundary

Return every order created during May

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.