Time truncation SARGability test

WITH
        E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT N, (N % 10) + (N % 33) AS SomeData, Convert(DateTime, RAND(convert(varbinary, newid())) * 10000) AS SomeDate
INTO #SomeTable
FROM cteTally
WHERE N < 1000000

--create a quite-specific index
CREATE INDEX SomeTable_TempIndex ON #SomeTable (SomeDate)

--search for single day using inequalities - SARGable, optimizer uses index + key lookup
SELECT * FROM #SomeTable WHERE SomeDate >= '1900-11-16' AND SomeDate < DateAdd(Day, 1, '1900-11-16')

--search for single day by truncating time from the column - non-SARGable, optimizer does a table scan, estimated cost 13X the previous version
SELECT * FROM #SomeTable WHERE DATEADD(DAY, DATEDIFF(DAY, 0, SomeDate), 0) = '1900-11-16'

-- do a date range search
SELECT * FROM #SomeTable WHERE SomeDate >= '1900-11-16' AND SomeDate < DateAdd(Day, 1, '1900-11-18')

-- do a date range search with (pointless) time truncation, just to check behaviour is consistent, without SARGing
SELECT * FROM #SomeTable WHERE DATEADD(DAY, DATEDIFF(DAY, 0, SomeDate), 0) >= '1900-11-16' AND DATEADD(DAY, DATEDIFF(DAY, 0, SomeDate), 0) <= '1900-11-18'

DROP TABLE #SomeTable