Be careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
So, by Using convert or casr in where clause how does it affect the index column.
It does not affect anything.
But it means that if there is an index on the column, that index cannot be used – leading to an expensive scan rather than (potentially) a seek.
Except - as Lynn pointed out - CASTing DATETIME column to DATE. It's SARGable.
Jeez... after more than 20 years of doing it the other way, I'm probably never going to get used to that exception. I went back and corrected my post above.
It's worth remembering, not just for this use case, but the other side of the predicate too. Try these on a table with a few million rows, with and without an index on the datetime column:
-- WITHOUT SUPPORTING INDEX
-- 1. Using CAST.
-- Fast: SQL Server can calculate CAST(GETDATE() AS DATE) and gets a good estimate of 149,798,000 rows
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < CAST(GETDATE() AS DATE) -- 00:00:03
-- 2. Using a parameter.
-- Fast: SQL Server compiles and reuses the plan using this value (parameter sniffing: ParameterRuntimeValue="'2017-10-17'")
DECLARE @TodayDate DATE = CAST(GETDATE() AS DATE)
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < @TodayDate -- 00:00:03
-- 3. Using a literal.
-- Fast: the estimate of 1 row is close to the actual number of 47 rows
-- The plan is quite different to cases 1 and 2 above.
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < '20051016' -- 00:00:03
-- 4. Using an expression.
-- Slow: SQL Server can't get anything from the date arithmetic and instead falls back on a default estimate of 1 row.
-- The compiled plan is the same as case 3 above, and it's totally inappropriate for the actual number of 149,797,717 rows.
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < DATEADD(day,DATEDIFF(day, 0, GETDATE()),0) -- 00:01:25