Sorry to slightly hijack this topic, but I've never understood this, am I missing something?

We're talking about comparing a column to a scalar value of a particular type, why does it matter how you arrive at your scalar value? Assuming it ends up as a type that is either the same, or has a lower data type precedence than the column it's comparing, it's SARGable.

An example below. I'm sure I'm missing something as people say all the time that you should use the dateadd method rather than convert, but I can't seem to get a non-SARGable version as long as you're not wrapping a function around the column, rather than the scalar part (in which case, both methods become unSARGable). If you need to get rid of the time part of a datetime/datetime2 column, converting to DATE is the only one that allows a seek in this case.

CREATE TABLE #TEST (DateColumn DATETIME2(0) NOT NULL PRIMARY KEY CLUSTERED);

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

),

E2(N) AS (SELECT 1 FROM E1 a, E1 b),

E4(N) AS (SELECT 1 FROM E2 a, E2 b)

--code pinched from Jeff's tally splitter

INSERT INTO #test

( DateColumn )

SELECT CAST(DATEADD(Day,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), GETDATE()-1000) AS DATE) FROM E4

--generate 10000 dates

--various different versions of comparing dates and their result (tested SQL 2008 SP3)

--dateadd

SELECT * FROM #test

WHERE DateColumn=DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

--seek

--convert to varchar

SELECT * FROM #test

WHERE DateColumn=CONVERT(VARCHAR(8),GETDATE(),112)

--seek (with implicit conversion)

--convert to varchar with explicit convert back to datetime

SELECT * FROM #test

WHERE DateColumn=CONVERT(DATETIME,CONVERT(VARCHAR(8),GETDATE(),112))

--seek

--convert to DATE data type

SELECT * FROM #test

WHERE DateColumn=CONVERT(DATE,GETDATE())

--seek

--variations with functions wrapped around the column rather than the scalar value

SELECT * FROM #test

WHERE CONVERT(VARCHAR(8),DateColumn,112)=CONVERT(VARCHAR(8),GETDATE(),112)

--scan

SELECT * FROM #test

WHERE DATEADD(dd, DATEDIFF(dd, 0, DateColumn), 0)=DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

--scan

SELECT * FROM #test

WHERE CONVERT(DATE,DateColumn)=CONVERT(DATE,GETDATE())

--seek (within a range)