The report was fast for three years. Someone added a year filter, and now it reads the whole table. The filter looks harmless:
WHERE YEAR(o.OrderDate) = 2026
There is a nonclustered index on OrderDate. The plan ignores it and scans. Nothing about the index changed, and nothing about the data changed. Wrapping the column in a function changed what the optimizer is allowed to do with it.
SQL Server date functions produce more non-SARGable predicates in production T-SQL than any other category of mistake, because the wrong version reads better. YEAR(OrderDate) = 2026 says what you mean. The version that seeks does not. What follows is organized by what you are trying to do, with the traps next to the functions that cause them.
Why the function on the column kills the seek
An index on OrderDate stores rows ordered by OrderDate. To seek, the engine needs a contiguous range of that key. YEAR(OrderDate) = 2026 asks for rows whose derived value equals 2026, and no index is ordered by the output of YEAR(). The engine reads every row, computes YEAR() on each, and throws away the misses.
Set up something you can run:
CREATE TABLE dbo.Orders
(
OrderID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL,
OrderDate datetime2(3) NOT NULL,
Amount decimal(19,4) NOT NULL
);
-- 220,924,800 seconds is 2,557 days: 2020-01-01 through 2026-12-31.
-- CHECKSUM can return -2147483648, and ABS() of that raises Msg 8115.
-- Masking the sign bit avoids it.
INSERT dbo.Orders (CustomerID, OrderDate, Amount)
SELECT TOP (2000000)
(CHECKSUM(NEWID()) & 2147483647) % 50000,
DATEADD(second, (CHECKSUM(NEWID()) & 2147483647) % 220924800, '20200101'),
(CHECKSUM(NEWID()) & 2147483647) % 100000 / 100.0
FROM sys.all_columns a CROSS JOIN sys.all_columns b;
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
ON dbo.Orders (OrderDate) INCLUDE (Amount);
SET STATISTICS IO ON;
Now compare the two forms:
-- Scans. Every row gets YEAR() applied to it.
SELECT COUNT(*), SUM(o.Amount)
FROM dbo.Orders AS o
WHERE YEAR(o.OrderDate) = 2026;
-- Seeks. One contiguous range of the index key.
SELECT COUNT(*), SUM(o.Amount)
FROM dbo.Orders AS o
WHERE o.OrderDate >= '20260101'
AND o.OrderDate < '20270101';
2026 is one of seven years in that data, so roughly 285,000 of the two million rows qualify. The first form reads all two million and discards 86 percent. The second walks the index to the first 2026 key and stops at the first 2027 key. Compare row counts on the two plans rather than the clock: two million into the aggregate against about 285,000.
Erik Darling tested what the optimizer guesses when you force it into the first shape. YEAR() gets estimates that at least vary by the year you ask for. MONTH() returns the same estimate for every month, because the statistics histogram carries no information about the month portion of a date. Combining both under the legacy cardinality estimator produced a guess of one row. Darling stops at the estimates. The downstream cost is the familiar one: a guess that low tends to buy a nested loop join and a serial plan on a table that wanted a hash join and parallelism.
A function on an indexed column is one instance of a general rule, and dates are where most people hit it first. For the rule itself, and for the wait statistics work that tells you which queries in your workload are paying for it, see how to find the queries that are scanning.
Where CONVERT(date, ...) is different
CONVERT(date, OrderDate) = @d behaves differently from YEAR(). SQL Server special-cases the conversion to date and builds a seek range at run time. Paul White documented the mechanism in 2012: an internal function, GetRangeThroughConvert, works out the range of column values covered by the date value, and the optimizer produces a plan that seeks. His worked case is a datetime2 column against a date variable, which is the same shape as the sample table above. SQLGrease reproduced it independently. You get a seek.
Two reasons to write the range anyway. White's own conclusion is that these hidden conversions "can result in inaccurate cardinality and distribution estimates at any stage of the plan. So, even if you get a seek, the plan might be way off overall." The optimization also stops the moment you nest anything else around it, or the column is a string someone converts first. Range predicates hold under all of those conditions.
The half-open range, and why BETWEEN loses rows
The pattern:
WHERE o.OrderDate >= @start
AND o.OrderDate < DATEADD(day, 1, @end)
Greater-or-equal on the low end, strictly-less-than on a boundary one unit past the high end. The predicate never mentions fractional seconds, so it does not care what precision the column has.
BETWEEN is inclusive on both ends, which is fine for date columns and wrong for everything else:
-- Misses every order placed on 31 March after 00:00:00.000
WHERE o.OrderDate BETWEEN '20260301' AND '20260331'
The repair most people reach for is worse:
-- Now it includes orders at midnight on 1 April
WHERE o.OrderDate BETWEEN '20260301' AND '20260331 23:59:59.999'
For a datetime column, 23:59:59.999 does not exist. datetime rounds to increments of .000, .003, and .007 seconds, and Microsoft's rounding table shows .999 going up to the next day at 00:00:00.000. Your March report now contains April orders. The same literal against datetime2(3) stores as written, so the query returns different row counts on two columns that look identical in the schema browser. Run it:
DECLARE @dt datetime = '20260331 23:59:59.999';
DECLARE @dt2 datetime2(3) = '20260331 23:59:59.999';
SELECT @dt AS AsDatetime, @dt2 AS AsDatetime2;
-- 2026-04-01 00:00:00.000 | 2026-03-31 23:59:59.999
Stop writing 23:59:59.997, 23:59:59.999, and DATEADD(ms, -3, ...). The half-open range removes the question.
Picking the type
| Type | Range | Accuracy | Bytes |
|---|---|---|---|
date |
0001-01-01 to 9999-12-31 | 1 day | 3 |
smalldatetime |
1900-01-01 to 2079-06-06 | 1 minute | 4 |
datetime |
1753-01-01 to 9999-12-31 | rounded to .000, .003, .007 second | 8 |
datetime2 |
0001-01-01 to 9999-12-31 | 100 ns | 6 to 8 |
datetimeoffset |
0001-01-01 to 9999-12-31 | 100 ns | 8 to 10 |
Microsoft's own guidance on the datetime page: "Avoid using datetime for new work." datetime2(3) gives you the same millisecond resolution in 7 bytes instead of 8, without the .003 rounding. datetime2(0) costs 6 bytes and holds whole seconds, which covers most order and audit tables.
The default is a trap. datetime2 with no precision means datetime2(7), 8 bytes, and seven decimals of a precision your application does not have. Declare the scale you need.
Use date for anything with no time component. A birth date stored as datetime invites = @d and returns zero rows when one record came in at 00:00:00.003.
datetimeoffset stores local time plus its offset from UTC. It does not store the zone, so it cannot tell you the offset after the next DST transition. That matters when you schedule future events.
Two consequences of this table show up later in the article rather than here. The datetime accuracy column is what makes BETWEEN '20260301' AND '20260331 23:59:59.999' return April rows, covered under the half-open range above. The week datepart of DATETRUNC moves with a session setting, covered under SET DATEFIRST below.
Column width is the other half of this decision and it belongs to a different argument: how the type you pick multiplies across every row, every index that carries it, and every page read to satisfy a scan. For sizing across the whole schema rather than the temporal corner of it, see choosing the column type before the functions matter.
Getting the current time
SELECT
GETDATE() AS GetDate, -- datetime, 3 decimals
SYSDATETIME() AS SysDateTime, -- datetime2(7), 7 decimals
GETUTCDATE() AS GetUtcDate, -- datetime, UTC
SYSUTCDATETIME() AS SysUtcDateTime, -- datetime2(7), UTC
SYSDATETIMEOFFSET() AS SysDateTimeOffset;-- datetimeoffset(7)
GETDATE() returns datetime and carries the rounding with it. SYSDATETIME() returns datetime2(7). Microsoft notes both call GetSystemTimeAsFileTime(), whose precision is fixed at 100 nanoseconds, and that accuracy depends on the hardware and Windows build. Seven decimals of output is not seven decimals of truth.
Assigning SYSDATETIME() into a datetime column drops you back to 3.33ms granularity with no warning. If you are logging events and want to order them, land them in datetime2.
SQL Server 2025 adds CURRENT_DATE, which returns date. On anything older, CAST(SYSDATETIME() AS date).
The working set of functions
DATEADD(datepart, number, date) shifts a date. It belongs on range boundaries because it operates on a variable, not the column.
DATEDIFF(datepart, startdate, enddate) counts boundary crossings, not elapsed time. DATEDIFF(year, '20251231', '20260101') returns 1 for a gap of one day. DATEDIFF(year, ...) can report 1 when one day has passed, so anyone computing age or tenure with it is off by up to a full year.
DATEPART(datepart, date) pulls a component out as an integer. DATENAME returns it as a string in the session language, which makes it unfit for anything you compare or sort.
EOMONTH(start_date [, month_to_add]) arrived in SQL Server 2012 and returns date. It replaces the old DATEADD/DATEDIFF dance for month ends, and the optional second argument shifts months first.
SELECT EOMONTH('20260215') AS ThisMonthEnd, -- 2026-02-28
EOMONTH('20260215', -1) AS LastMonthEnd, -- 2026-01-31
EOMONTH('20240215') AS LeapYearEnd; -- 2024-02-29
DATEFROMPARTS(year, month, day), also 2012, builds a date from integers and raises an error on invalid input rather than guessing. Use it instead of concatenating strings and converting.
DATETRUNC(datepart, date) shipped in SQL Server 2022. It returns the same type and fractional scale as its input, and supports year, quarter, month, dayofyear, day, week, iso_week, hour, minute, second, millisecond, and microsecond. It does not support weekday, timezoneoffset, or nanosecond.
SELECT DATETRUNC(month, CAST('20260317 14:22:09' AS datetime2(3)));
-- 2026-03-01 00:00:00.000
One datepart carries the session-local problem described further down. Microsoft's DATETRUNC page says of week: "In T-SQL, the first day of the week is defined by the @@DATEFIRST T-SQL setting." DATETRUNC(week, ...) moves when SET DATEFIRST moves. iso_week does not, because "the first day of the week in the ISO8601 calendar system is Monday."
DATE_BUCKET(datepart, number, date [, origin]) also arrived in 2022, for fixed-width buckets. Its origin defaults to 1900-01-01 00:00:00.000.
On 2019 and older, the month-truncation idiom is:
SELECT DATEADD(month, DATEDIFF(month, 0, @d), 0);
The 0 is 1900-01-01. It works, it is fast, and it belongs in the SELECT list rather than the WHERE clause.
DATEDIFF overflows, and it is closer than you think
DATEDIFF returns int. Microsoft states the limits: for millisecond, the maximum difference between the two dates is 24 days, 20 hours, 31 minutes, and 23.647 seconds. For second, it is 68 years, 19 days, 3 hours, 14 minutes, and 7 seconds. Past those you get an error, not a wrong number.
A job that measures elapsed milliseconds against a record from last month raises Msg 535, Level 16: "The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart." The fix landed in SQL Server 2016:
SELECT DATEDIFF_BIG(millisecond, '20200101', SYSDATETIME());
DATEDIFF_BIG returns bigint and overflows only at nanosecond precision beyond roughly 292 years.
SET DATEFIRST makes DATEPART(weekday) a session-local answer
Microsoft's documentation is direct: for the week and weekday dateparts, the DATEPART return value depends on SET DATEFIRST. The default under us_english is 7, meaning Sunday. Under British it is 1, Monday. The setting is session-scoped and applies at execute time.
A stored procedure filtering on DATEPART(weekday, o.OrderDate) = 2 returns Mondays for one application pool and Tuesdays for another, depending on the login's default language. Nobody finds this in testing, because the test connection and the application connection share a default.
Prove it:
SET DATEFIRST 7; -- US default
SELECT @@DATEFIRST AS DateFirst, DATEPART(weekday, '20260316') AS Weekday; -- 7, 2
SET DATEFIRST 1; -- Monday first
SELECT @@DATEFIRST AS DateFirst, DATEPART(weekday, '20260316') AS Weekday; -- 1, 1
Same date, two answers. Two fixes. Use DATEPART(iso_week, ...) where ISO week numbering fits, or normalize against the setting:
-- 1 = Monday, regardless of DATEFIRST
SELECT ((DATEPART(weekday, o.OrderDate) + @@DATEFIRST - 2) % 7) + 1
FROM dbo.Orders AS o;
DATEDIFF is immune. Microsoft states it always uses Sunday as the first day of the week to keep the function deterministic, so SET DATEFIRST has no effect on it.
Write date literals as 'YYYYMMDD'
'2026-03-05' is not safe against a datetime column. Microsoft's datetime page says the ISO 8601 form is unaffected by SET DATEFORMAT and SET LANGUAGE, and then specifies what the ISO 8601 form requires: "you must specify each element in the format, including the T, the colons (:), and the period (.)". A bare hyphenated date is not that form, so datetime parses it under the session's date format. Aaron Bertrand tested 'YYYY-MM-DD' against datetime across all 34 SQL Server languages and found it unsafe in 24 of them, safe in 10. His test harness covers datetime only. Answering a reader in the comments on that article, he adds that the newer types were fixed: "They fixed this behavior with the newer types like date and datetime2, but we'll be stuck with datetime and smalldatetime for years to come."
date and datetime2 do not have this problem, and the sourcing for that deserves to be exact, because it is not one documentation sentence. Microsoft's date page lists yyyy-MM-dd under ISO 8601 with no language-independence note attached. The datetime2 page makes that statement only for the form including the T. For the bare hyphenated form on date and datetime2, the claim rests on Bertrand's comment above and on practitioner reproductions. Run the snippet below on your own build first.
The consequence is that one literal means one thing against OrderDate datetime and another against OrderDate datetime2, which is a hard failure to reason about at 2am.
'20260305' works everywhere. The date documentation is explicit: a six-digit or eight-digit unseparated string is always interpreted as ymd. Microsoft's guidance page on international T-SQL recommends unseparated numeric strings for scripts, procedures, and triggers. The same page makes a second recommendation the first one tends to overshadow: use CONVERT with an explicit style code for conversions between the date types and character strings. Use eight digits for literals, and an explicit style when you convert.
SET LANGUAGE British;
SELECT CAST('20260305' AS datetime) AS Unseparated; -- 2026-03-05, always
SELECT TRY_CAST('2026-03-05' AS datetime) AS Hyphenated; -- depends on the session
AT TIME ZONE, and where to put it
AT TIME ZONE arrived in SQL Server 2016. Applied to a datetime2, it attaches an offset and assumes the value was already in that zone. Applied to a datetimeoffset, it converts. The DST rules come from the Windows registry, exposed through sys.time_zone_info.
The pattern for UTC storage and local display:
-- dbo.OrderEvents.OccurredUtc is datetime2(3) holding UTC.
-- The sample dbo.Orders above has no UTC column; this is a separate table.
SELECT
e.OrderID,
e.OccurredUtc,
e.OccurredUtc AT TIME ZONE 'UTC'
AT TIME ZONE 'Eastern Standard Time' AS OccurredLocal
FROM dbo.OrderEvents AS e
WHERE e.OccurredUtc >= @startUtc
AND e.OccurredUtc < @endUtc;
Two clauses. The first labels the stored value as UTC, the second converts. One clause alone gives you a value with the wrong offset attached and no error.
Keep it off the per-row path. Jonathan Kehayias traced the cost: the conversion "relies on the time zones that are stored in the Windows Registry and therefore has to make calls out to Windows which unfortunately occurs row-by-row for large result sets when used inside of a query." Grouping 9.8 million rows by OrderDate AT TIME ZONE 'Pacific Standard Time' burned 221,500 ms of CPU on his eight-core machine, 3 minutes 41 seconds of wall clock. One measurement, two units.
Check what he measured before copying the fix. None of his three queries has a WHERE clause. All three are SELECT ... GROUP BY with the conversion in the select list and the grouping key, so the number is the cost of converting every row, not the cost of filtering on a converted column. His 4.5-second result came from pulling a fixed offset out of sys.time_zone_info into variables and applying it with DATEADD, and he capitalizes the warning that goes with it: "HOWEVER, THIS IS NOT THE CORRECT WAY TO DO THINGS ACROSS DATE RANGES!" A single offset is the wrong offset on one side of a DST transition.
The mechanism transfers to a WHERE clause even though the benchmark does not. A per-row call out to Windows is expensive, so keep the conversion off the column you filter on. Store UTC, convert your parameters once, filter on the raw stored column, and convert on the way out so the work runs against the rows you return. Measure that rewrite on your own data, because nobody has published a like-for-like benchmark of it.
Worked example: monthly revenue
The version that arrives in code review:
SELECT
YEAR(o.OrderDate) AS OrderYear,
MONTH(o.OrderDate) AS OrderMonth,
SUM(o.Amount) AS Revenue
FROM dbo.Orders AS o
WHERE YEAR(o.OrderDate) = 2026
GROUP BY YEAR(o.OrderDate), MONTH(o.OrderDate)
ORDER BY OrderYear, OrderMonth;
The version that runs:
SELECT
DATEADD(month, DATEDIFF(month, 0, o.OrderDate), 0) AS MonthStart,
SUM(o.Amount) AS Revenue
FROM dbo.Orders AS o
WHERE o.OrderDate >= '20260101'
AND o.OrderDate < '20270101'
GROUP BY DATEADD(month, DATEDIFF(month, 0, o.OrderDate), 0)
ORDER BY MonthStart;
On 2022 and later the grouping expression becomes DATETRUNC(month, o.OrderDate), which reads better and does the same work. The two are not interchangeable in output: against a datetime2(3) column DATETRUNC returns datetime2(3), while DATEADD(month, DATEDIFF(month, 0, col), 0) returns datetime. Check what your report layer does with the difference.
The functions did not disappear. They moved from the WHERE clause to the SELECT and GROUP BY, where they run against the rows that survived the filter.
Turn on SET STATISTICS IO ON and actual plans, then run both. Expect the first query to show an Index Scan on IX_Orders_OrderDate feeding all two million rows into the aggregate. Expect the second to show an Index Seek with a Seek Predicate section listing a start of OrderDate >= '2026-01-01' and an end of OrderDate < '2027-01-01', feeding about 285,000. The reads follow the rows. Click the leftmost SELECT operator, open its properties, and compare Estimated Number of Rows against Actual on the aggregate. Those are the two numbers worth writing down, because they are the ones that stay true when your table is not this table.
Scale it up. At 200 million rows across ten years, the scan reads all ten years to answer a question about one. The seek reads a tenth. That ratio is the whole argument, and it holds regardless of what your hardware does with it.
This query returns one row per month, which is the shape a report layer then has to transpose. If the next request is twelve month columns across a single row per product or region, the grouping expression here becomes the column source for the transpose: see spreading the monthly result across columns.
A procedure for auditing date predicates
- Find the predicates.
sys.dm_exec_query_statsranks your top consumers bytotal_logical_readsbut holds no query text, so join to it:
SELECT TOP (50)
qs.total_logical_reads,
qs.execution_count,
st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_logical_reads DESC;
Search the results for YEAR(, MONTH(, DATEPART(, CONVERT( and CAST( sitting to the left of a comparison operator.
2. Rewrite each one as >= @start AND < DATEADD(day, 1, @end). Move the function to the SELECT or GROUP BY.
3. Delete every BETWEEN against a datetime, datetime2, or datetimeoffset column.
4. Replace every 23:59:59.997 and 23:59:59.999 literal with a half-open upper bound.
5. Rewrite every date literal as 'YYYYMMDD'.
6. Search for DATEPART(weekday and DATEPART(dw. Replace with iso_week or the @@DATEFIRST normalization above.
7. Search for DATEDIFF(millisecond and DATEDIFF(second. On 2016 and later, change to DATEDIFF_BIG.
8. Search for AT TIME ZONE in WHERE clauses. Convert the parameters instead.
9. Re-run with actual plans. Confirm each rewritten predicate shows a Seek Predicate, not a Predicate.
Step 9 is the one people skip. A plan that still scans after the rewrite means something else is converting the column, and the PlanAffectingConvert warning in the plan XML names it. That element is documented in practitioner write-ups and an archived Microsoft blog rather than current Learn pages, so search the XML for it directly.
One failure mode survives every step above. A date column is an ascending key: today's inserts land above the top step of the histogram, so until statistics update, the optimizer is estimating rows in a range its histogram does not describe yet. The predicate seeks and the estimate is still wrong, which is the version of this problem that hits reports filtered to the last hour or the current day. That is a statistics problem rather than a predicate problem, and it is treated in why estimates go wrong on ascending date keys.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.