Filter by Date Without Missing the Last Day
sqlSQLbeginnerinterview-prepbest-practices
A half-open date range that works whether the column holds dates or date-times, and that can still use an index.
-- Orders placed in October 2026, whatever the time of day.
-- Half-open range: on or after the first day, and strictly before the first day of the next month.
-- For DATETIME columns in SQL Server, write literals as 'YYYYMMDD': it is read the same way
-- under every language setting.
SELECT id, order_date, total
FROM orders
WHERE order_date >= '20261001'
AND order_date < '20261101';
-- Avoid: WHERE order_date BETWEEN '20261001' AND '20261031'
-- BETWEEN includes the end value only at 00:00:00, so it misses most of 31 October.
-- Avoid: WHERE YEAR(order_date) = 2026 AND MONTH(order_date) = 10
-- Wrapping the column in functions prevents an index seek.