Functions on Columns and Date Ranges

Date filtering is where non-SARGable predicates appear most often, because the natural way to express "orders in 2026" or "today's activity" reaches for a function. Reports are almost entirely date filters, so this is where a reporting query most often loses its index.

The half-open range pattern

Learn one pattern and it covers every case:

WHERE OrderDate >= @Start
  AND OrderDate <  @EndExclusive

Greater-than-or-equal at the start, strictly-less-than at the end, with the end being the start of the next period. Never BETWEEN on a datetime column.

BETWEEN '2026-01-01' AND '2026-01-31' on a datetime2 column silently excludes everything from 2026-01-31 00:00:00.0000001 onward — nearly a full day of data missing from the report, with no error. People "fix" this with '2026-01-31 23:59:59.997', which is both wrong-looking and precision- dependent: it is correct for datetime (3.33 ms granularity) and wrong for datetime2(7). The half-open range is correct at every precision and reads clearly.

Rewriting the common ones

-- A calendar year
--  before: WHERE YEAR(OrderDate) = 2026
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'

-- A month
--  before: WHERE YEAR(OrderDate)=2026 AND MONTH(OrderDate)=3
WHERE OrderDate >= '2026-03-01' AND OrderDate < '2026-04-01'

-- One day, from a date parameter
--  before: WHERE CAST(OrderDate AS date) = @Day
WHERE OrderDate >= @Day AND OrderDate < DATEADD(DAY, 1, @Day)

-- Last 30 days
--  before: WHERE DATEDIFF(DAY, OrderDate, GETDATE()) <= 30
WHERE OrderDate >= DATEADD(DAY, -30, CAST(SYSDATETIME() AS date))

-- Current month, computed rather than hard-coded
DECLARE @MonthStart date = DATEFROMPARTS(YEAR(SYSDATETIME()), MONTH(SYSDATETIME()), 1);
WHERE OrderDate >= @MonthStart AND OrderDate < DATEADD(MONTH, 1, @MonthStart)

Note that DATEADD(DAY, -30, ...) on the right-hand side is fine. The function is applied to a constant, evaluated once at compile time, and compared against the bare column. Only functions wrapping the column break SARGability.

Use unambiguous date literals: '2026-01-01' (ISO) or '20260101'. '01/02/2026' is interpreted differently depending on the session's language and DATEFORMAT settings, which produces reports that are correct on your machine and wrong on the server.

String functions, same rule

-- before: WHERE LEFT(ProductCode, 3) = 'ABC'
WHERE ProductCode LIKE 'ABC%'          -- leading-anchored LIKE seeks fine

-- before: WHERE UPPER(LastName) = 'SMITH'
WHERE LastName = 'SMITH'               -- default collations are case-insensitive

Check the collation before removing an UPPER():

SELECT name, collation_name FROM sys.databases WHERE name = DB_NAME();
-- '..._CI_...' = case-insensitive; '..._CS_...' = case-sensitive

Removing UPPER() under a case-sensitive collation changes results, which is a different problem from being slow.

When you genuinely need the computed value

Sometimes the business logic really is "group by fiscal quarter" or "find rows where the trimmed code matches." Two tools:

Persisted computed column plus an index. Materialize the expression once per row instead of once per query:

ALTER TABLE dbo.Orders
    ADD OrderYearMonth AS (CONVERT(char(6), OrderDate, 112)) PERSISTED;   -- placeholder table

CREATE NONCLUSTERED INDEX IX_Orders_YearMonth
    ON dbo.Orders (OrderYearMonth) INCLUDE (Total);

-- Now this is SARGable
SELECT SUM(Total) FROM dbo.Orders WHERE OrderYearMonth = '202603';

The expression must be deterministic and precise for this to be indexable, and the column costs storage and a little write time. In exchange the computation happens once, at write, instead of on every row of every report.

Index the expression directly via a computed column, even without changing the query. If you add a persisted computed column whose definition exactly matches an expression in your WHERE clause, the optimizer can match the existing query text to the computed column and use its index without you rewriting the query. This is useful when the query lives in third-party code — including a report definition you cannot easily edit.

-- Query text stays as: WHERE YEAR(OrderDate) = 2026
ALTER TABLE dbo.Orders ADD OrderYear AS (YEAR(OrderDate)) PERSISTED;
CREATE NONCLUSTERED INDEX IX_Orders_OrderYear ON dbo.Orders (OrderYear) INCLUDE (Total);

Matching requires the SET options to line up and the expression to be written identically, so verify with an actual plan rather than assuming. When it works, it is the only way to make an unmodifiable query SARGable.

A date dimension is not a substitute

Reporting schemas often join to a DimDate table and filter on DimDate.FiscalQuarter. That is a legitimate design, but check the plan: filtering the dimension and joining back to a large fact table on a date key works well when the fact table's index leads with that key, and works poorly when the optimizer decides to scan the fact table and hash it against the dimension. Include the fact table's own date range in the predicate as well when you can — giving the optimizer a direct range on the fact table is worth the redundancy.

Rule of thumb

For date filtering, use a half-open range on the bare column — greater-or-equal the start, strictly less than the start of the next period. That is SARGable, and it is also correct at any datetime precision, unlike BETWEEN with an end-of-day literal, which quietly drops the last fraction of a day. When the report genuinely needs a computed value, add a persisted computed column and index that, so the work happens once per row at write time instead of once per row per report.