SQL Quest › SQL Interview Questions › SQL traps › BETWEEN on timestamps

SQL BETWEEN with timestamps: why the last day goes missing

When a column holds a time as well as a date, BETWEEN '…' AND '2026-03-31' stops at the first instant of March 31, so the whole last day is left out.

The task

Count the card transactions in March 2026. txn_at is stored as a full ISO timestamp, such as 2026-03-31T23:06:59.458Z. The data is SQL Quest's synthetic card-transactions set (accounts, merchants, transactions, chargebacks), and every result on this page is what the query returns on it.

The query that looks right

SELECT COUNT(*) AS transactions
FROM transactions
WHERE txn_at BETWEEN '2026-03-01' AND '2026-03-31';

Returns: 918

918 transactions.

Why

The fix

Fix 1 — A half-open range: from the first day, up to (not including) the next month

SELECT COUNT(*) AS transactions
FROM transactions
WHERE txn_at >= '2026-03-01'
  AND txn_at <  '2026-04-01';

Returns: 960

Fix 2 — Or compare the date part only

SELECT COUNT(*) AS transactions
FROM transactions
WHERE DATE(txn_at) BETWEEN '2026-03-01' AND '2026-03-31';

Returns: 960

Right answer: March 2026 has 960 transactions.

Rule of thumb. For time ranges, use >= start AND < next_start. It is correct for dates, timestamps and strings alike, and it can use an index on the column.

Where this trap is tested

This trap is question 4 of our Capital One CodeSignal-style mock, and again in question 8 of our Capital One CodeSignal-style mock — a timed practice screen on the same kind of data. The mock asks it in a different form, so this page does not give its answer away.

Practise it

Related SQL traps