Date Math in the WHERE Clause: Writing SARGable Date Filters in SQL Server

“This month’s orders”, “yesterday’s transactions”, “invoices from the last 30 days”… Date filters may be the most common part of queries in business applications. And one of the most common performance mistakes hides right here: wrapping the date column in a function. Predicates like YEAR(OrderDate) = 2026 or CONVERT(varchar(10), OrderDate, 101) = '10/16/2026' return the right result, but they stop SQL Server from using the index on that column and make it scan the whole table.

This article explains why such date predicates can’t use an index, how to write them SARGable (searchable with an index), and the boundary mistakes common in date ranges.

Why the Index Can’t Be Used

An index is sorted by the column’s raw values. An index on OrderDate keeps the dates in order, but it doesn’t keep YEAR(OrderDate) values. When the column is inside a function in the predicate, SQL Server has to compute the function for every row and compare the result, which means an index or table scan instead of an index seek.

The rule is simple: leave the column alone and move the calculation to the other side.

Common Patterns and Their SARGable Equivalents

-- BAD: year
WHERE YEAR(OrderDate) = 2026
-- GOOD
WHERE OrderDate >= '20260101' AND OrderDate < '20270101'

-- BAD: month
WHERE YEAR(OrderDate) = 2026 AND MONTH(OrderDate) = 10
-- GOOD
WHERE OrderDate >= '20261001' AND OrderDate < '20261101'

-- BAD: last 30 days (calculation on the column side)
WHERE DATEADD(DAY, 30, OrderDate) >= GETDATE()
-- GOOD: calculation on the constant side
WHERE OrderDate >= DATEADD(DAY, -30, GETDATE())

-- BAD: comparing as text
WHERE CONVERT(varchar(8), OrderDate, 112) = '20261016'
-- GOOD
WHERE OrderDate >= '20261016' AND OrderDate < '20261017'

Dynamic Ranges: This Month, Last Month, Today

Compute the range boundaries once in variables, then compare the column with them:

DECLARE @today          date = CAST(GETDATE() AS date);
DECLARE @monthStart     date = DATEFROMPARTS(YEAR(@today), MONTH(@today), 1);
DECLARE @lastMonthStart date = DATEADD(MONTH, -1, @monthStart);

-- This month's orders
SELECT OrderNo, Amount FROM dbo.Orders
WHERE OrderDate >= @monthStart AND OrderDate < DATEADD(MONTH, 1, @monthStart);

-- Last month's orders
SELECT OrderNo, Amount FROM dbo.Orders
WHERE OrderDate >= @lastMonthStart AND OrderDate < @monthStart;

-- Today's orders
SELECT OrderNo, Amount FROM dbo.Orders
WHERE OrderDate >= @today AND OrderDate < DATEADD(DAY, 1, @today);

A Common Boundary Mistake: BETWEEN

BETWEEN includes both ends. On a column with a time component, BETWEEN '20261001' AND '20261031' only picks up 31 October’s rows at exactly midnight and misses the rest of the day. Writing '20261031 23:59:59' to fix it isn’t safe either: because of datetime’s precision, 23:59:59.997 can round, and with datetime2, rows between 23:59:59 and midnight are missed.

Writing Date Literals

Formats such as '16.10.2026' or '10/16/2026' can be interpreted differently depending on the session’s language and DATEFORMAT setting; a query that works on one server can fail on another or pick the wrong month. Use language-independent formats: '20261016' (date only) or '2026-10-16T14:30:00' (date and time).

One Exception: CAST(column AS date)

Converting a datetime or datetime2 column to date is a case SQL Server specifically recognizes, and an index seek is still possible. Even so, the estimated row count can be less accurate; the range pattern is the safer habit for both readability and plan quality.

Checklist

• The date column isn’t wrapped in a function in WHERE predicates.
• Ranges use the “start inclusive, end exclusive” pattern; BETWEEN isn’t used on date-time columns.
• Dynamic boundaries are computed once in variables.
• Date literals use a language-independent format (YYYYMMDD).
• The execution plan shows an index seek on the date filter.

Because the column is inside a function, SQL Server can’t use the index and computes the function for every row. Write date >= ‘20260101’ AND date < '20270101' instead.[/arya_faq_item] [arya_faq_item q="What does SARGable mean?"]Search ARGument-able: the predicate can be evaluated with an index seek. The column must not be wrapped in a function or calculation.[/arya_faq_item] [arya_faq_item q="Should I use BETWEEN for date ranges?"]Not on columns with a time component. The date >= start AND date < day-after-end pattern gives correct results for every type and precision.[/arya_faq_item] [arya_faq_item q="How should date literals be written in SQL Server?"]In the language-independent YYYYMMDD format ('20261016') or ISO 8601 format ('2026-10-16T14:30:00').[/arya_faq_item] [/arya_faq]