Using functions on columns typically prevents SQL Server from being able to efficiently use an index with a seek (sargability). When it comes to DATE and DATETIME data types, there are some exceptions.
A common use case I see is where a column contains a DATETIME data type; however, a query needs to look at a date without the time part. The following is a typical query that would do this:
SELECT * from CustomerAccount WHERE CONVERT(DATE, ModifiedDate) = '2022-01-30'
I would typically expect something like this to scan the ModifiedDate column due to a function (CONVERT) being applied to that column. It turns out SQL Server makes some exceptions to this where it can still seek on the indexed ModifiedDate:
You can see the seek is performing a range > and < on the ModifiedDate column:
So if you have complex logic like this to avoid conversions on a DATETIME column it’s not necessary:
SELECT * from CustomerAccount WHERE ModifiedDate >= CONVERT(DATE, @ModifiedDate) AND ModifiedDate < DATEADD(dd, 1, CONVERT(DATE, @ModifiedDate))