Your Date Filter Is Silently Dropping a Whole Day

The January report was missing 4% of revenue. Nobody found it for six months. The culprit was the word BETWEEN.

The problem

Pull every order in January. Everybody writes this:

WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

If order_date carries a time component, this only reaches 00:00:00 on the 31st. Every order placed during that day vanishes.

The fix: a half-open range

WHERE order_date >= '2026-01-01'
  AND order_date <  '2026-02-01';

The >= start AND < next pattern is correct for every data type and time zone, and it still uses the index. Make it a reflex.

The second common mistake: wrapping the column

-- Index ignored, full table scan
WHERE YEAR(order_date) = 2026 AND MONTH(order_date) = 1

-- Index used
WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01'

General rule: keep the column bare on the left. Move every transformation to the right side.

The functions worth memorising

Grouping by month, correctly

-- WRONG: merges January across every year
GROUP BY TO_CHAR(order_date, 'Mon')

-- RIGHT
SELECT DATE_TRUNC('month', order_date) AS month, SUM(total)
FROM orders
GROUP BY 1
ORDER BY 1;

Group and sort on the underlying value; format only at the final display step.

Practice

• LeetCode 197 — Rising Temperature (compare against the previous day)
• LeetCode 1141 — User Activity for the Past 30 Days I
• LeetCode 1907 — Count Salary Categories
• LeetCode 1683 — Invalid Tweets
• HackerRank — SQL Project Planning