Every SQL Server developer learns the rule: don't wrap a column in a function inside your WHERE clause, or the optimizer abandons the fast Index Seek for a full Index Scan that reads every row. And everyone learns the famous exception: a date conversion like CAST(OrderDate AS DATE) = '2026-06-18' still seeks.

But "date conversion" hides two genuinely different situations, and they behave differently under the hood. Converting a datetime column down to date is not the same operation as converting a date column up to datetime, even though the syntax looks identical. This article tests both independently, so each stands on its own.

A shared note on method: each scenario uses about two million rows spread across five years, so that a single day is highly selective. Selectivity is the precondition for a seek — on a tiny table a scan can be the genuinely cheaper plan, and that would be the optimizer being right, not a SARGability failure. Every query is measured three ways: the plan operator (Seek vs Scan), logical reads via SET STATISTICS IO ON, and CPU time via SET STATISTICS TIME ON.

Scenario 1:  DATETIME -> DATE

 Here the column stores a full timestamp, and we convert it down to a date to match a date data type.

Setup

The table below is built to look like a realistic OLTP order table, not a synthetic edge case: OrderID is a surrogate INT identity acting as the clustered primary key (the default and most common clustering choice in practice). OrderDate is declared DATETIME, so it carries a genuine time-of-day component, and TotalAmount plays no role in the SARGability question. It exists only to force a key lookup, since the single non-clustered index, IX_OrderDate, covers OrderDate alone with no INCLUDE columns, and that lookup cost shows up as extra logical reads in the Results section.

The table is then populated with two million rows spread across five years (1,826 days) using a doubling CTE trick (L0 through L5) to generate row numbers without a physical numbers table, so that the test day, 2026-06-18, represents a genuinely selective ~1-in-1,826 slice -- selective enough that the optimizer should prefer a seek on cost alone, rather than a scan winning legitimately on a small or non-selective table. Because that day's row count is otherwise left to chance, a second insert adds roughly 500 more rows explicitly dated 2026-06-18 with varied times of day, guaranteeing a known, repeatable row count and avoiding an all-midnight coincidence that would make CAST(OrderDate AS DATE) a trivial exact match rather than a genuine same-day one

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
    DROP TABLE dbo.Orders;
GO
CREATE TABLE dbo.Orders
(
    OrderID INT IDENTITY(1, 1) PRIMARY KEY, -- clustered PK
    OrderDate DATETIME NOT NULL,            -- the column under test (has a time component)
    TotalAmount DECIMAL(18, 2) NOT NULL
);

GO
CREATE NONCLUSTERED INDEX IX_OrderDate ON dbo.Orders (OrderDate);
GO
--------------------------------------------------------------------------------------

SET NOCOUNT ON;
DECLARE @RowCount INT = 2000000;

DECLARE @SpanSeconds BIGINT = 1826 * 86400; -- 2022-01-01 .. 2026-12-31
;
WITH L0
AS (SELECT 1 AS c
    UNION ALL
    SELECT 1),
     L1
AS (SELECT 1 AS c
    FROM L0 a
        CROSS JOIN L0 b),
     L2
AS (SELECT 1 AS c
    FROM L1 a
        CROSS JOIN L1 b),
     L3
AS (SELECT 1 AS c
    FROM L2 a
        CROSS JOIN L2 b),
     L4
AS (SELECT 1 AS c
    FROM L3 a
        CROSS JOIN L3 b),
     L5
AS (SELECT 1 AS c
    FROM L4 a
        CROSS JOIN L4 b),
     Nums
AS (SELECT TOP (@RowCount)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM L5)
INSERT INTO dbo.Orders
(
    OrderDate,
    TotalAmount
)
SELECT DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % @SpanSeconds, '2022-01-01T00:00:00'),
       CAST(ABS(CHECKSUM(NEWID())) % 1000000 / 100.0 AS DECIMAL(18, 2))
FROM Nums;

GO

-- ~500 rows with varied times on the test day

INSERT INTO dbo.Orders
(
    OrderDate,
    TotalAmount
)
SELECT DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % 86400, '2026-06-18T00:00:00'),
       CAST(ABS(CHECKSUM(NEWID())) % 1000000 / 100.0 AS DECIMAL(18, 2))
FROM
(
    SELECT TOP (500)
           1 AS c
    FROM sys.all_objects a
        CROSS JOIN sys.all_objects b
) x;

GO

UPDATE STATISTICS dbo.Orders
WITH FULLSCAN;

GO

The Tests

Each test below runs the same query with a different WHERE clause against 2026-06-18, with SET STATISTICS IO/TIME on to capture reads, CPU, and the plan operator.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

Test A: the baseline explicit range, no function on the column

We expect this to use an index seek

SELECT OrderID, OrderDate, TotalAmount
 FROM dbo.Orders
 WHERE OrderDate >= '2026-06-18' AND OrderDate < '2026-06-19';
GO

The plan pane labels the operator Index Seek (NonClustered), and the Seek Predicates panel on the right lists the two boundaries exactly as written in the query — '2026-06-18' and '2026-06-19' — confirming the optimizer used them directly with no rewriting involved.

Test B: CAST(OrderDate AS DATE)

This is the classic "date conversion still seeks" case.

SELECT OrderID, OrderDate, TotalAmount
 FROM dbo.Orders
 WHERE CAST(OrderDate AS DATE) = '2026-06-18';
GO

SSMS still reports an Index Seek, but the Predicate box shows the query normalized internally to CONVERT(date, OrderDate) = '2026-06-18', and the Seek Predicates use two placeholders, [Expr1005] and [Expr1006], instead of literal dates — a sign the boundaries are being computed rather than supplied directly.

Test C: the same idea with CONVERT(DATE, ...) instead of CAST.

This query uses a function in the WHERE clause.

SELECT OrderID, OrderDate, TotalAmount
 FROM dbo.Orders
 WHERE CONVERT(DATE, OrderDate) = '2026-06-18';
GO

The plan is the same shape as CAST's: an Index Seek fed by the same [Expr1005]/[Expr1006] placeholders, confirming the optimizer treats the two syntaxes identically.

Test D: String Searches

This query converts the column to a string instead of a date.

SELECT OrderID, OrderDate, TotalAmount
 FROM dbo.Orders
 WHERE CONVERT(VARCHAR(10), OrderDate, 120) = '2026-06-18';
GO

The plan pane now shows a Clustered Index Scan running under Parallelism, and the Predicate box lists CONVERT(varchar(10), OrderDate, 120) = '2026-06-18' — the engine is evaluating that expression against every row rather than seeking a range.

Test E: Searching on Date Parts

This query splits the date into YEAR/MONTH/DAY parts.

SELECT OrderID, OrderDate, TotalAmount
 FROM dbo.Orders
 WHERE YEAR(OrderDate) = 2026 AND MONTH(OrderDate) = 6 AND DAY(OrderDate) = 18;
GO

The plan again shows a Clustered Index Scan, and the Predicate box lists three separate DATEPART comparisons — one each for year, month, and day — chained together with AND, confirming that splitting the date into parts also forces a scan.

Test F: Stripping off Time

This query uses the older DATEADD/DATEDIFF midnight-strip idiom.

SELECT OrderID, OrderDate, TotalAmount
 FROM dbo.Orders
 WHERE DATEADD(DAY, DATEDIFF(DAY, 0, OrderDate), 0) = '2026-06-18';
GO

The Predicate box shows the DATEADD(DAY, DATEDIFF(DAY, 0, OrderDate), 0) expression evaluated against every row, and the plan shape — a Clustered Index Scan under Parallelism — matches D and E exactly.

Results

All six returned the same 1,539 rows, so they are directly comparable.

The seek/scan wall lands exactly where the theory predicts. CAST and CONVERT to DATE (B, C) keep the seek and match the hand-written range (A); string conversion, part extraction, and date arithmetic (D, E, F) all fall to a full scan.

CPU time is the sharpest signal. The seeks cost essentially nothing — 0 to 16 ms — because the engine jumps straight to the qualifying range. The scans cost 454 to 970 ms, because each evaluates a function on all two million rows before it can compare anything. The scan count confirms it: 1 for a single seek, 9 for a scan fanned across parallel workers.

Two details worth calling out. First, the seeks report thousands of reads rather than a dozen, because the queries also select TotalAmount, which isn't in the index. The plan is really an Index Seek plus a key lookup per row, and those lookups are the bulk of the reads. Adding INCLUDE (TotalAmount) would reduce them.

Second, in this run, A read noticeably more pages than B and C (7,223 vs 4,636) while still being unmistakably a seek — scan count 1 and 16 ms of CPU, nowhere near the 11,073-read scans. Logical reads can vary between runs and plan variants for the same logical query, so the read column separates seeks from scans reliably, but small differences between the seeks should not be over-interpreted; the operator in the actual execution plan is the authoritative verdict.

Why the String Conversion Scans

It is tempting to assume dates are stored as text — you type '2026-06-18 ', and the grid shows '2026-06-18'. But those are only the input and output skins. Internally, SQL Server stores dates as packed integers, and the index is a B-tree sorted by that integer. A DATE is a 3-byte count of days since 0001-01-01; a DATETIME is an 8-byte pair of a day count and a tick count since midnight. Crucially, that number increases monotonically with the date. Earlier dates are smaller integers, and later dates larger. The integer is what lets a seek binary-search the key.

That ordering is the dividing line. A seek can only survive a conversion that preserves the order of the underlying integer. CAST(datetime AS date) does, so the optimizer expresses it as a range against the raw key and seeks. CONVERT(VARCHAR(10), OrderDate, 120) does the opposite: it renders the integer into characters, moving the comparison into string space, where the index ordering no longer applies (and where, with a different style code, '06/18/2026' would even sort before '06/19/1999' despite being the later date). Unable to assume the text projection preserves the key's order, the optimizer computes the string for every row and compares and does a full scan. The queries with YEAR/MONTH/DAY and DATEADD/DATEDIFF scan for the same reason: they are computed projections the optimizer won't treat as order-equivalent to the key. It is not that converting to a string "has issues" — it is that dates are ordered numbers, a seek needs the conversion to keep that order, and rendering to text throws the order away.

Inside the Plan Rewrite

Reading the cached plan for the CAST query reveals the mechanism. The seek does not run against literal dates — it runs against two computed expressions:

OrderDate > [Expr1005]  AND  OrderDate < [Expr1006]

The boundaries are computed at runtime rather than folded in as constants. This is an internal conversion routine, visible in the Compute Scalar's defined values, derives which slice of the datetime domain maps to that one date. The operators are strict GT (>) and LT (<), not >= and <, and the range is exclusive at both ends, with the lower boundary nudged one tick below midnight so the strict > still admits a midnight row. Semantically, it is the inclusive-start, exclusive-end day you expect, implemented with two strict inequalities.

Because those boundaries must be produced somewhere, the plan contains a Constant Scan, an operator that reads no table, but manufactures a single row so a Compute Scalar can build the boundary expressions. The shape is: Constant Scan -> Compute Scalar -> Nested Loops -> Index Seek. This Constant Scan appears only in B and C. The explicit range A doesn't need it (its boundaries are literal constants), and D, E, F produce no range seek at all. The presence of that extra branch is the reliable fingerprint of this rewrite — open the actual execution plan for B or C, and it is right there on the left of the seek.

Scenario 2: date -> datetime

Here the column stores only a date, and we convert it to datetime. The syntax mirrors Scenario 1, but the mechanism is entirely different.

Setup

This table mirrors Scenario 1's shape almost exactly except for one change that is the entire point of this scenario: OrderDate is declared DATE instead of DATETIME, a type with no time-of-day component at all, which flips the conversion direction under test from narrowing a timestamp down to a date to widening a date up to a timestamp. It uses the same surrogate INT identity clustered PK and a non-clustered index on OrderDate alone, and it's populated with the identical two-million-row random-timestamp generation script and the same ~500-row targeted insert for 2026-06-18 used in Scenario 1 -- the random times generated in the SELECT are silently truncated to bare dates on insert because of the column type -- so that the column's data type is the only real difference between the two experiments, not how the data was built.

IF OBJECT_ID('dbo.Orders_ReverseTesting', 'U') IS NOT NULL
    DROP TABLE dbo.Orders_ReverseTesting;
GO
CREATE TABLE dbo.Orders_ReverseTesting
(
    OrderID INT IDENTITY(1, 1) PRIMARY KEY, -- clustered PK

    OrderDate DATE NOT NULL,                -- the column under test (no time component)

    TotalAmount DECIMAL(18, 2) NOT NULL
);

GO
CREATE NONCLUSTERED INDEX IX_OrderDate
ON dbo.Orders_ReverseTesting (OrderDate);
GO

SET NOCOUNT ON;
DECLARE @RowCount INT = 2000000;
DECLARE @SpanSeconds BIGINT = 1826 * 86400; -- 2022-01-01 .. 2026-12-31;
WITH L0
AS (SELECT 1 AS c
    UNION ALL
    SELECT 1),
     L1
AS (SELECT 1 AS c
    FROM L0 a
        CROSS JOIN L0 b),
     L2
AS (SELECT 1 AS c
    FROM L1 a
        CROSS JOIN L1 b),
     L3
AS (SELECT 1 AS c
    FROM L2 a
        CROSS JOIN L2 b),
     L4
AS (SELECT 1 AS c
    FROM L3 a
        CROSS JOIN L3 b),
     L5
AS (SELECT 1 AS c
    FROM L4 a
        CROSS JOIN L4 b),
     Nums
AS (SELECT TOP (@RowCount)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM L5)
INSERT INTO dbo.Orders_ReverseTesting
(
    OrderDate,
    TotalAmount
)
SELECT DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % @SpanSeconds, '2022-01-01T00:00:00'),
       CAST(ABS(CHECKSUM(NEWID())) % 1000000 / 100.0 AS DECIMAL(18, 2))
FROM Nums;

GO

INSERT INTO dbo.Orders_ReverseTesting
(
    OrderDate,
    TotalAmount
)
SELECT DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % 86400, '2026-06-18T00:00:00'),
       CAST(ABS(CHECKSUM(NEWID())) % 1000000 / 100.0 AS DECIMAL(18, 2))
FROM
(
    SELECT TOP (500)
           1 AS c
    FROM sys.all_objects a
        CROSS JOIN sys.all_objects b
) x;

GO
UPDATE STATISTICS dbo.Orders_ReverseTesting
WITH FULLSCAN;
GO

The Tests

The same six queries, run against the DATE column instead of the DATETIME one. As before we turn on IO and time tracking.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

Test A: the same baseline explicit range

SELECT OrderID, OrderDate, TotalAmount
 FROM dbo.Orders_ReverseTesting
 WHERE OrderDate >= '2026-06-18' AND OrderDate < '2026-06-19';
GO

The plan pane again shows an Index Seek, and the Seek Predicates panel lists the same literal boundaries used in the query, '2026-06-18' and '2026-06-19' — no rewriting was needed.

Test B: CAST(OrderDate AS DATETIME) — widening instead of narrowing, the reverse of Scenario 1's Test B.

SELECT OrderID, OrderDate, TotalAmount
 FROM dbo.Orders_ReverseTesting
 WHERE CAST(OrderDate AS DATETIME) = '2026-06-18';
GO

This is also a plain Index Seek, and its Predicate box shows the query normalized to CONVERT(datetime, OrderDate, 0) = '2026-06-18 00:00:00.000' — but unlike Scenario 1, the Seek Predicates use that single literal value directly, with no [Expr] placeholders and no Constant Scan anywhere in the plan.

Test C: the same idea with CONVERT(DATETIME, ...)

SELECT OrderID, OrderDate, TotalAmount
 FROM dbo.Orders_ReverseTesting
 WHERE CONVERT(DATETIME, OrderDate) = '2026-06-18';
GO

CONVERT(DATETIME, OrderDate) produces the identical plan shape: a single literal value in the Seek Predicates and no Constant Scan, matching CAST's plan exactly.

Test D: the same string-conversion predicate as Scenario 1.

SELECT OrderID, OrderDate, TotalAmount
 FROM dbo.Orders_ReverseTesting
 WHERE CONVERT(VARCHAR(10), OrderDate, 120) = '2026-06-18';
GO

The plan switches to a Clustered Index Scan, and the Predicate box shows CONVERT(varchar(10), OrderDate, 120) = '2026-06-18' evaluated row by row — the same pattern as Scenario 1's equivalent test.

Test E: the same YEAR/MONTH/DAY split.

SELECT OrderID, OrderDate, TotalAmount
 FROM dbo.Orders_ReverseTesting
 WHERE YEAR(OrderDate) = 2026 AND MONTH(OrderDate) = 6 AND DAY(OrderDate) = 18;
GO

The Predicate box again lists three separate DATEPART comparisons chained with AND, under a Clustered Index Scan — the same shape as Scenario 1's Test E.

Test F: the same DATEADD/DATEDIFF idiom.

SELECT OrderID, OrderDate, TotalAmount
 FROM dbo.Orders_ReverseTesting
 WHERE DATEADD(DAY, DATEDIFF(DAY, 0, OrderDate), 0) = '2026-06-18';
GO

The DATEADD(DAY, DATEDIFF(DAY, 0, OrderDate), 0) expression is again evaluated against every row under a Clustered Index Scan, matching D and E.

Results

All six returned the same 1,555 rows. As in Scenario 1, the seeks (A, B, C) cost far less than the scans (D, E, F) — but the numbers themselves differ: every seek here reads exactly 4,783 pages, versus 7,223 or 4,636 in Scenario 1, and the costliest scan, D, runs to 1,391 ms, longer than any test in Scenario 1.

 Interpreting the Results

Which tests seek and which tests scan matches Scenario 1 exactly: A, B, and C seek; D, E, and F scan. But the reason B and C seek here is completely different from Scenario 1, and the numbers show it.

Look at the three seeks: A, B, and C read an identical 4,783 pages , the explicit range from test A; the CAST and the CONVERT all cost exactly the same index work, page for page. That is the tell: no rewrite is involved. There is no Constant Scan, no Compute Scalar building boundary expressions, because none is needed. Open the actual execution plans for B and C, and they are the same bare Index Seek as A.

The reason is structural. datetime? date (Scenario 1) is a narrowing, many-to-one conversion — one date covers a whole day of timestamps, so the optimizer must rebuild that interval, which is what the Constant Scan and computed range do. date? datetime (this scenario) is the opposite: a widening, one-to-one, order-preserving conversion. One date maps to exactly one datetime — midnight — so there is nothing to reconstruct. The optimizer simply seeks the single equivalent value, and CAST, CONVERT, and the explicit range all resolve to the same trivial seek. That is why an explicit CAST around the column (B), normally the textbook "you just killed your seek" move, doesn't kill anything here.

The killers behave exactly as in Scenario 1: string conversion, part extraction, and date arithmetic move the comparison off the ordered key and force a full scan, with the same per-row CPU penalty (704 ms to 1.4 s versus 0–16 ms for the seeks). The same storage-and-ordering principle from Scenario 1 applies unchanged — a seek needs the conversion to preserve the key's integer order, and only the date-type conversions do.

Putting the Two Scenarios Together

Both directions preserve the seek, but by different mechanisms, and the execution plans reveal which is which:

• Forward (datetime -> date) seeks via a dynamic range rewrite — a Constant Scan feeds computed GT/LT boundaries into the seek. Its fingerprint is the extra Constant Scan -> Compute Scalar branch visible in the execution plan for the CAST/CONVERT queries.
• Reverse (date -> datetime) seeks via ordinary order-preserving widening — no rewrite, no Constant Scan. Its fingerprint is that CAST, CONVERT, and the explicit range all produce the same bare seek and read identically (4,783 pages each in this run).
• In both directions, the real seek-killers are the same three: CONVERT to VARCHAR, YEAR/MONTH/DAY, and DATEADD/DATEDIFF — because each moves the comparison off the index's ordered integer key.

The practical takeaway is unchanged by direction: prefer the explicit half-open range, WHERE col >= '2026-06-18' AND col < '2026-06-19'. It seeks whether the column is a date or datetime; it doesn't rely on the optimizer's rewrite, and it ports cleanly to other database engines that won't perform the same magic.