Level 5 · December is short by eleven orders

October and November match the order system, December is short. When only the edge of a date range is wrong, look at how the range ends.

What BETWEEN means on a timestamp

order_date is a TIMESTAMP. Comparing it with the string '2025-12-31' turns the string into 2025-12-31 00:00:00, the very first second of the day. BETWEEN is inclusive on both ends, but the end is midnight, so it keeps only orders placed at exactly 00:00:00 on Dec 31.

On Dec 31, 2025 there were 25 orders between 05:18 and 23:23. Eleven of them were completed, and they are exactly the gap: 623 versus 634 orders, and $281,531.76 versus $288,591.06 of revenue. Over the full year, BETWEEN '2025-01-01' AND '2025-12-31' finds 4,758 orders of every status instead of 4,783.

The equality version of the same bug

SELECT COUNT(*) FROM orders WHERE order_date = '2025-11-28';

This returns 0 for Black Friday, which had 56 orders. Equality with a date string only matches timestamps at midnight.

The fix: half-open ranges

WHERE o.order_date >= '2025-10-01'
  AND o.order_date < '2026-01-01'

Start inclusive, end exclusive, at the first moment you do not want. It works for dates and timestamps alike, needs no 23:59:59 guesswork, and month ranges chain without gaps or overlaps. For a single day, >= '2025-11-28' AND < '2025-11-29' returns the 56 orders.

Casting the column, CAST(order_date AS DATE) BETWEEN ..., also gives the right rows, but it applies a function to every row and stops many databases from using an index on order_date.

Common mistakes

• <= '2025-12-31 23:59:59': misses anything with fractional seconds after 23:59:59.
• BETWEEN '2025-10-01' AND '2026-01-01': inclusive, so it would pull in orders placed at exactly midnight on New Year’s Day.
• Fixing the WHERE but not checking the GROUP BY: here the month truncation was already right.

In SQL Server

DATETIME and DATETIME2 behave the same way: BETWEEN '2025-10-01' AND '2025-12-31' stops at midnight. Use the same half-open range, or compare on CAST(order_date AS date) for one-off checks:

WHERE order_date >= '2025-10-01' AND order_date < '2026-01-01'

On the job

“The last day is short” is so common that many teams ban BETWEEN on timestamps in code review.