BETWEEN two dates in SQL Server: the inclusive trap
BETWEEN is inclusive on both sides. On a date column that is usually what you wanted. On a datetime, the end is midnight of that day, so almost the whole last day goes missing.
What people write
-- Looks like 1–31 October. Drops almost all of the 31st if order_date has a time.
WHERE order_date BETWEEN '2026-10-01' AND '2026-10-31''2026-10-31' becomes 2026-10-31 00:00:00. An order at 09:14 on the 31st is after that, so it is out.
The half-open range
WHERE order_date >= '2026-10-01'
AND order_date < '2026-11-01'First moment of the 1st, up to but not including the 1st of next month. Every clock time on the 31st is in.
BETWEEN DATEADD(day, -7, GETDATE()) AND GETDATE() has a time on both ends. You lose the start of the first day and you include this morning. Use midnight bounds instead. See last week’s orders.
Try it on Harbour & Co
James Rowe (Sales Manager) needs this now: “Need last week’s orders over £50 for the 10am. Don’t include today. Newest first.” Write the query, run it, send the results. Free.
Take the ticket →Learn it on a real ticket
Monday is free on WorkSQL. A colleague’s ticket lands, you write the T-SQL, we mark it. No videos. Five minutes.
Clock in free See Monday’s tickets