SQL ·
SQL BETWEEN dates - why the whole last day goes missing
A July revenue report ran BETWEEN two date literals and quietly dropped the last day, flipping which region led. Here is the half-open range that fixes it.
Test yourself before reading - the write-up below gives it away.
-- July revenue by region, paid orders only.
SELECT region,
ROUND(SUM(amount), 2) AS revenue
FROM orders
WHERE placed_at BETWEEN '2026-07-01' AND '2026-07-31'
AND status = 'paid'
GROUP BY region
ORDER BY revenue DESC;
The July revenue-by-region report is due before the first Monday standup in August, the morning finance closes last month's books. It has run the same way for eleven months: pull the range, group by region, sort by whoever led. This July the date literals rolled forward like always, one month later than June's, and the query ran clean against a live production database.
Here is the query, meant to total July's paid orders by region:
-- July revenue by region, paid orders only.
SELECT region,
ROUND(SUM(amount), 2) AS revenue
FROM orders
WHERE placed_at BETWEEN '2026-07-01' AND '2026-07-31'
AND status = 'paid'
GROUP BY region
ORDER BY revenue DESC;
Why does SQL BETWEEN with dates miss the last day of the range?
In SQL, BETWEEN '2026-07-01' AND '2026-07-31' treats the end date as midnight, the instant July 31 begins, not the whole day. Any row timestamped later that day falls outside the range and vanishes from the result. The fix is a half-open range: placed_at >= '2026-07-01' AND placed_at < '2026-08-01', which works for DATE, DATETIME, and string columns alike.
The end date in BETWEEN means midnight, not the whole day
Line 5 is the bug. BETWEEN is honest about what it does: it keeps both endpoints, no more and no less. PostgreSQL's own manual spells out the substitution:
"a BETWEEN x AND y is equivalent to a >= x AND a <= y. Notice that BETWEEN treats the endpoint values as included in the range." - PostgreSQL: Comparison Functions and Operators
'2026-07-31' compared against a date-and-time value names one instant, the first moment of July 31, 00:00:00. <= keeps that instant and rejects everything after it, so an order logged at 09:05 or 14:30 the same day fails the upper bound and drops out of the SUM entirely. The ROUND(SUM(amount), 2) on line 3 is the line that looks like the arithmetic worth distrusting, and it rounds whatever total reaches it correctly.
This is not a quirk of how the demo database stores dates. placed_at here is ISO-8601 text, so SQLite compares it as a string, and '2026-07-31' is a strict prefix of '2026-07-31 09:05:00', so the shorter string sorts first and the timestamped row loses the comparison. Give the same query a real DATE or TIMESTAMP column in PostgreSQL, MySQL, or SQL Server, and the literal '2026-07-31' still parses to midnight before the comparison runs. The column type changes how the loss happens; it does not change that it happens.
The bug survives review because most test fixtures place their last row safely inside the month, hours before midnight, or skip a time component altogether and let every date collapse to 00:00:00, a boundary where the truncation costs nothing to check. The gap only opens once a real timestamp lands after midnight on the boundary day, which for any table logging events around the clock is most days.
One missing day flips which region wins the month
Follow the report to where it lands. Ops reviews the region ranking every month and shifts next quarter's ad spend toward whichever region led; the sorted output is the decision input, not a chart nobody reads. Run the buggy query against a July of paid orders that includes two orders placed the afternoon of July 31, one from each region, and EU comes back on top: 200.5 against US's 200, a fifty-cent margin that reads as a coin flip already leaning EU. A number that plausible does not get rerun.
Swap the BETWEEN for the half-open range and the same July returns US at 340, EU at 295.75. The two orders that fell out at the midnight boundary were each worth more than the entire visible gap, and recovering them reverses the ranking instead of just narrowing it. The mismatch only turns up when someone happens to rebuild the query with a different WHERE clause, a new analyst reaching for >= out of habit or a quarterly audit rerunning the numbers, and the two totals refuse to agree on who led. A single order at August 1, 00:30 stays excluded under both versions, for different reasons: the buggy query excludes it because midnight August 1 is past the old upper bound too, the fixed one because < '2026-08-01' was written to stop exactly there.
The fix: a half-open range, not a longer date string
Replace the closed range with one open end:
SELECT region,
ROUND(SUM(amount), 2) AS revenue
FROM orders
WHERE placed_at >= '2026-07-01'
AND placed_at < '2026-08-01'
AND status = 'paid'
GROUP BY region
ORDER BY revenue DESC;
| region | BETWEEN (buggy) | half-open (fixed) |
|---|---|---|
| EU | 200.5 | 295.75 |
| US | 200.0 | 340.0 |
The tempting patch is to keep BETWEEN and stretch the end date instead: BETWEEN '2026-07-01' AND '2026-07-31 23:59:59'. It recovers the two orders in this example and looks like the smaller edit, one longer string literal. It still has an edge, just a narrower one: a row logged at 23:59:59.842 sorts after '2026-07-31 23:59:59' for the same reason the 09:05 order sorted after plain '2026-07-31', so any column with sub-second precision reopens the identical gap at the millisecond scale instead of the day scale. Older SQL Server advice papered over this by rounding the literal up to '23:59:59.997', the highest value the legacy DATETIME type's own rounding can represent - tuned to one type's internal precision, and wrong the moment the column is datetime2 or the query runs on a different engine entirely. < '2026-08-01' needs none of that arithmetic; it catches every fractional second of July 31 without asking what precision the column stores.
Find truncating BETWEEN date filters in your codebase
No linter flags this, because BETWEEN between two date literals is valid SQL doing precisely what it was told. Grep for the shape instead:
grep -rniE "between\s+'[0-9]{4}-[0-9]{2}-[0-9]{2}'\s+and\s+'[0-9]{4}-[0-9]{2}-[0-9]{2}'" --include=*.sql .
Check ORM call sites too, .between(start, end) in SQLAlchemy or field__range=(start, end) in Django build the identical closed range under a friendlier name. For each hit, check the column's actual type and whether anything in that table ever carries a time component after midnight on the end date. A pure DATE column with no time part is safe as written; anything logging real timestamps is a candidate.
Related bugs
A BETWEEN clause is not the only place SQL drops rows while doing exactly what it was told: a NULL-aware comparison excludes rows a plain reader expects to keep, and integer division throws away a fraction the same way a date literal throws away a day. More live on the SQL bug hub, and the rest of the write-ups sit at bug write-ups.
Next month's close runs the same query, both date literals rolled forward again. Before it runs, swap the closed range for a start you keep and an end you exclude - the report closes on time either way, but only one version closes on the truth.