Something that many don't know about SARGability is that some functions, regardless of side of equation in a Where clause, are SARGable.

Try this:

SET NOCOUNT ON;

USE ProofOfConcept;

GO

CREATE TABLE dbo.SARGTest (

DT DATETIME NOT NULL);

GO

CREATE CLUSTERED INDEX CID_SARGTest_DT ON dbo.SARGTest(DT);

GO

INSERT INTO dbo.SARGTest(DT)

SELECT TOP (1000000) DATEADD(DAY, CHECKSUM(NEWID())%10000, GETDATE())

FROM ProofOfConcept.dbo.Numbers AS N1

CROSS JOIN ProofOfConcept.dbo.Numbers AS N2;

GO

SELECT *

FROM dbo.SARGTest

WHERE DT >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

AND DT < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1);

SELECT *

FROM dbo.SARGTest

WHERE CAST(DT AS DATE) = CAST(GETDATE() AS DATE);

Check the execution plans on both of the final queries. We know the first query will result in an index seek. It follows the usual rules for SARGability. What's surprising to many is that the second one, with CAST() on the left (and right) of the Where clause, also results in an index seek.

(Actual execution plans attached.)

Also tested:

DECLARE @s-2 DATE = GETDATE(), @E DATE = DATEADD(DAY, 1, GETDATE());

SELECT *

FROM dbo.SARGTest

WHERE DT >= @s-2 AND DT < @E;

Still get a seek. (See Plan2.sqlplan, attached.)