Filtering on Dates Without Losing the Index

The quickest way to lose a date index is to wrap its column in a function. Filtering on dates deserves a clear range. Date and time boundaries create another trap: the last instant of a day depends on the data type. A half-open range handles both problems cleanly.

Why Functions on Columns Hurt Filtering on Dates

An index on OrderDate is ordered by the stored values. A predicate such as CONVERT(date, OrderDate) = @Day asks SQL Server to transform values before comparing them. The optimizer can sometimes derive a useful range from a conversion, but you should not rely on that special case for every expression. Functions such as YEAR and MONTH commonly block a direct seek.

I write predicates against the original column whenever possible. That makes the search boundary obvious to both the optimizer and the next reader. Then I inspect the actual plan, seek predicate, and logical reads. The appearance of an Index Seek alone does not prove the filter is efficient if a large residual predicate remains. Where does your report’s end boundary fall when the column stores fractions of a second?

Use a Half-Open Day Range

For all rows on one calendar day, start at midnight inclusive and stop at the next midnight exclusive. This includes every representable time on the requested day without guessing the final fraction of a second. It works for datetime, datetime2, and date columns, subject to type-compatible parameters. The upper bound is computed from the parameter, not from each table row.

A supporting index on OrderDate can seek into the range. Test with real data density, including a day with many rows and a day with few. A range seek that returns half the table can still cost more than a scan, and that can be the optimizer’s correct choice.

DECLARE @Day date = '2026-09-01';
SELECT OrderID, OrderDate, TotalAmount
FROM dbo.Orders
WHERE OrderDate >= @Day
  AND OrderDate < DATEADD(day, 1, @Day);

Avoid End-of-Day Guesswork When Filtering on Dates

A predicate ending at 23:59:59.997 reflects the old datetime precision, but datetime2 can store later fractions. Ending at 23:59:59.9999999 requires knowing the target type and scale. Either pattern is brittle when a column type changes. The next midnight as an exclusive boundary expresses the actual business interval.

BETWEEN includes both endpoints. For contiguous daily windows, that can count the boundary row twice if one window ends at midnight and the next begins there. Half-open ranges compose cleanly: the first stops where the second starts. The arithmetic is simple, which is a rare gift from date logic.

Match Parameter and Column Types

Implicit conversions can interfere with index use when the parameter type has higher precedence or incompatible formatting. Use typed date or datetime2 parameters that match the column and avoid string concatenation in application code. SQL Server accepts ISO-style date literals in examples, but production code should bind parameters with explicit types.

If a column stores UTC timestamps, calculate boundaries in UTC for the user’s requested local day. Converting every stored timestamp to local time in the WHERE clause can make the index work harder. Compute the two UTC instants once, then filter the column between them. Daylight saving changes make that boundary calculation a real requirement.

Filter a Month the Same Way

A month is another half-open interval. Start at the first day and stop at the first day of the next month. Avoid YEAR(OrderDate) = @Year AND MONTH(OrderDate) = @Month on a large table when an indexed range can express the same question. Check the input month and year before building the start date.

This form also makes partition elimination easier when partitions align with the date column and boundaries. A partitioned table still needs good indexing and a useful predicate. Partitioning does not make a nonsearchable expression fast by itself.

DECLARE @MonthStart date = DATEFROMPARTS(2026, 9, 1);
SELECT OrderID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @MonthStart
  AND OrderDate < DATEADD(month, 1, @MonthStart);

Consider Date-Only Columns

If the column is genuinely a date with no time component, equality on a typed date value is clear and searchable. Do not force every date-only query into a range for style. The half-open pattern becomes especially valuable when time is stored or when one API must handle several temporal data types consistently.

A persisted computed date column with its own index can support recurring date-only grouping or filters, but it adds storage and write cost. Consider it when the workload proves the need and simple ranges cannot serve the query. An extra schema object is not a substitute for fixing an avoidable function in one predicate.

Check the Plan and Row Counts

Use an actual execution plan and compare estimated and actual rows at the access operator. Inspect Seek Predicates and Predicate properties separately. An Index Seek can navigate to a broad range and then evaluate a residual expression on many rows. Logical reads reveal how much of the index was touched, while duration can be distorted by caching and concurrency.

I test both the old and revised predicates with the same representative values. A single date with no rows is a poor benchmark. Include a busy date, a quiet date, and an interval crossing a month boundary. Confirm the returned rows match before celebrating a faster plan.

Keep Reporting Semantics Explicit

A business day is not always a midnight-to-midnight interval in the database’s time zone. Overnight shifts, trading sessions, and tenant-specific zones require boundaries defined by the business. Calculate those boundaries outside the indexed column predicate, then use the same half-open comparison. Indexing cannot repair an interval whose meaning was never specified.

When reports group by day, filtering and grouping can have different transformations. It is reasonable to group a narrowed result by a derived date, while retaining a searchable range in WHERE. Separate those jobs in the query. A function in SELECT or GROUP BY is not automatically the same problem as a function applied to every row in a broad filter.

Reuse the Same Pattern for Filtering on Dates

Store start-inclusive and end-exclusive semantics in API contracts and report definitions. That removes ambiguity when several services ask for the same period. Review query plans after changing column types or time zone handling because implicit conversions can reappear. A simple boundary convention makes both correctness and performance easier to audit.

Filtering on dates is a common source of accidental scans because the slow version looks natural. The fix is usually a clear interval, typed parameters, and a supporting index chosen for the workload. Give the optimizer ordered values to search, then verify that the plan does exactly that.

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

Filtering on dates is not formatting timestamps, it is defining searchable time boundaries.

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