SARGable Date Filters: Stop Wrapping Columns in Functions

The date column has an index, yet a small report reads far too much. SARGable date filters give SQL Server usable search boundaries. Keep the column unchanged and calculate the boundaries separately.

Separate the Column from the Calculation

I check date predicates when a selective report scans a large index. YEAR(OrderDate) describes the right business question. It usually hides the column behind a calculation that prevents a straightforward range seek.

The optimizer's final access choice still depends on the whole query. A large requested range can make a scan reasonable. A range predicate creates an opportunity, not an unconditional promise of a seek.

Some conversions have special optimizer behavior. Converting datetime to date can produce a dynamic seek. Even then, explicit boundaries make the intended interval easier to reason about.

Use a disposable database for the sample. The generated dates are test inputs, not an observed workload. Open an actual execution plan before running the comparisons.

CREATE TABLE dbo.DateFilterDemo
(
    OrderId int NOT NULL PRIMARY KEY,
    OrderDate datetime2(3) NOT NULL,
    OrderAmount decimal(12,2) NOT NULL
);
INSERT dbo.DateFilterDemo(OrderId, OrderDate, OrderAmount)
SELECT TOP (3000) ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id),
       DATEADD(day, ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id),
               CONVERT(datetime2(3), '20200101')),
       10.00
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
CREATE INDEX IX_DateFilterDemo_Date
ON dbo.DateFilterDemo(OrderDate) INCLUDE(OrderAmount);

Give Yearly SARGable Date Filters Two Boundaries

A year starts at one date and ends before the next year's first date. The lower boundary is inclusive. The upper boundary is exclusive.

This half-open shape handles every representable time on the final day. It doesn't depend on guessing the last fraction of a second. Data type precision stops being part of the filter's business rule.

The following statements ask for the same calendar year. Compare logical reads and the operator's predicate properties. Don't stop at the cost percentages drawn over the plan.

The function version can read a broad access path and filter afterward. The range version allows an indexed interval. Your measured plan determines what happened on your table.

SET STATISTICS IO ON;
SELECT SUM(OrderAmount) AS YearAmount
FROM dbo.DateFilterDemo
WHERE YEAR(OrderDate) = 2025;
SELECT SUM(OrderAmount) AS YearAmount
FROM dbo.DateFilterDemo
WHERE OrderDate >= CONVERT(datetime2(3), '20250101')
  AND OrderDate < CONVERT(datetime2(3), '20260101');
SET STATISTICS IO OFF;

Use SARGable Date Filters for a Month

A month begins on its first date and ends before the next month's first date. DATEADD handles the transition into another year. February doesn't need a special last-day branch.

Calculate both boundaries once. Use parameters with types compatible with the column. Don't place a conversion around OrderDate to compensate for a poorly typed parameter.

SARGable date filters also make review easier. The reader can see the requested month without inspecting a nested function. The predicate remains useful when the column's precision changes.

This example uses September as sample input. The query doesn't depend on September having a particular number of days. DATEADD supplies the correct next boundary.

DECLARE @MonthStart datetime2(3) = '20250901';
DECLARE @MonthEnd datetime2(3) = DATEADD(month, 1, @MonthStart);
SELECT OrderId, OrderDate, OrderAmount
FROM dbo.DateFilterDemo
WHERE OrderDate >= @MonthStart AND OrderDate < @MonthEnd;

Treat a Single Day as an Interval

A date-only comparison against datetime needs a whole day's interval. Equality against midnight includes only midnight values. That mistake produces a fast query with the wrong answer.

BETWEEN includes both endpoints. Using tomorrow's midnight as its upper endpoint includes a row from tomorrow. The less-than upper predicate avoids that overlap.

Which time zone defines your reporting day? A UTC column and a local business day need converted boundaries. Compute the UTC start and end from the business zone before searching.

Daylight saving changes can make a local day shorter or longer. Adding twenty-four hours to an arbitrary UTC point doesn't always describe that local day. Keep zone rules outside the indexed column.

DECLARE @Day date = '20250923';
DECLARE @DayStart datetime2(3) = CONVERT(datetime2(3), @Day);
DECLARE @DayEnd datetime2(3) = DATEADD(day, 1, @DayStart);
SELECT OrderId, OrderDate
FROM dbo.DateFilterDemo
WHERE OrderDate >= @DayStart AND OrderDate < @DayEnd;

Define What Last Thirty Days Means

A rolling window ending now differs from thirty completed calendar days. Decide which one the report requires. Both fit the same half-open predicate shape.

For a rolling UTC window, capture SYSUTCDATETIME once. Subtract thirty days for the start. Use the captured instant as the exclusive upper boundary.

For completed calendar days, use today's midnight as the end. Subtract thirty calendar dates in the reporting zone. Convert those zone boundaries when the stored values use UTC.

I ask this question before comparing plans because different answers select different rows. A performance rewrite must preserve the intended interval. A faster wrong date range earns no applause.

DECLARE @WindowEnd datetime2(3) = SYSUTCDATETIME();
DECLARE @WindowStart datetime2(3) = DATEADD(day, -30, @WindowEnd);
SELECT SUM(OrderAmount) AS RollingAmount
FROM dbo.DateFilterDemo
WHERE OrderDate >= @WindowStart AND OrderDate < @WindowEnd;

Compare SARGable Date Filters on Equivalent Work

Use the same selected columns and boundaries in each comparison. A covering query and a SELECT * query aren't equivalent tests. Lookup work can dominate the difference.

Read STATISTICS IO output beside the actual plan. Logical reads measure page accesses, including cached pages. Physical reads depend on the cache state during the test.

Inspect estimated and actual rows near the access operator. A poor estimate deserves a statistics and parameter review. Changing the predicate shape doesn't repair every distribution problem.

Avoid clearing production caches for this experiment. Run comparisons on a representative copy when you need controlled conditions. Record the input boundaries with the result.

Also compare the returned rows before celebrating a plan change. Date strings in ambiguous formats introduce another correctness risk. Typed parameters and unambiguous sample literals keep that risk outside the test.

A year crossing leap day deserves its own boundary test. A month crossing December deserves one too. These checks verify calendar semantics rather than chasing an attractive seek icon.

Keep the Rule beside the Report

I prefer named boundary parameters over repeated nested expressions. They make test cases easier to review. They also keep time zone decisions visible to the next maintainer.

Test values exactly at both boundaries. Include a fractional time near the end. Also test a NULL date if the table permits one.

SARGable date filters preserve an index-friendly column expression and an understandable calendar rule. Review both sides of that statement. Performance and correctness belong in the same test.

Use a scan when the optimizer chooses one for broad work. Fix accidental scans when a narrow interval should support less work. The goal is the right result with appropriate access.

A NULL timestamp doesn't belong to a dated interval. If the report needs undated rows, request them through a separate explicit rule. Don't change the interval predicate to conceal missing dates.

Related reading on this blog: Catching Non-SARGable Queries in Action and Optimize DATE in WHERE Clause: SQL in Sixty Seconds #189.

A date filter is not a last-second guessing game, it is an interval with clear boundaries.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.