Execution plans are great but sometimes they just don't tell you the truth.
So here is a proof based on performance test.
(and the bottom line - the expression is not being evaluated for every row)
;with t(i) as (select 0 union all select i+1 from t where i < 9)
select getdate()-1 as col1,getdate() as col2,getdate() as col3
into #t
from t t0,t t1,t t2,t t3,t t4,t t5,t t6,t t7
(100000000 row(s) affected)
This is the OP query and it takes around 12 seconds to run
SELECT col1
FROM #t
WHERE
DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
BETWEEN col2
AND col3
;
This query which stores the date in a parameter before the execution, takes about the same time, 12 seconds.
declare @dt datetime = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
SELECT col1
FROM #t
WHERE
@dt
BETWEEN col2
AND col3
;
And just to verify the results -
This query which does the computation on col1 and therefore have to recalculate the expression for every row takes about 30 seconds to run.
SELECT col1
FROM #t
WHERE
DATEADD(dd, 0, DATEDIFF(dd, 0, col1))
BETWEEN col2
AND col3
;
All queries were executed repeatedly showing about the same metrics