9.1 How to Filter a Date Range the Safe Way (>= Start AND < Next)

Why. Monthly reports are wrong if the last day of the month is missing. This happens often with date-time text.

What. posted_at holds a date and a time. SQLite compares these values as text. The text '2026-09-30 15:30:00' is longer than '2026-09-30' and starts the same way, so it sorts after it. That is why BETWEEN '2026-09-01' AND '2026-09-30' leaves out every row on 30 September that has a time, even 00:00:00. The safe pattern is >= the first day AND < the first day of the next month. It works here because every posted_at value uses the same format, YYYY-MM-DD HH:MM:SS, on the same clock. If a table mixes formats or time zones, clean them into one format first.

How. Count September rows with BETWEEN and with the safe pattern, and compare the two counts.

SELECT COUNT(*) AS rows_found
FROM transactions
WHERE posted_at BETWEEN '2026-09-01' AND '2026-09-30';

SELECT COUNT(*) AS rows_found
FROM transactions
WHERE posted_at >= '2026-09-01'
  AND posted_at <  '2026-10-01';

SELECT txn_id, posted_at, amount
FROM transactions
WHERE posted_at >= '2026-09-30'
  AND posted_at <  '2026-10-01';