I have been reviewing code from last couple of years. I found some myths are followed very strictly by developers. Yesterday while reviewing, I found developers are hesitating to use TRY_CAST or CAST function on column in where clause to convert date to datetime or datetime to date. When I asked the reason they said use of function on column will not use index created on that column.

It is true that use of function on columns in search predicate or join condition will not pick the correct index. This is true for almost all cases except date or datetime datatype column. Let’s run the below code. For below example I am using AdventureWorks DB ( it can be downloaded from codeplex.

Now enable actual execution plan and run below two queries.

Query 1:

 

CAST 1

Here, though we use CAST function on column ModifiedDate, it is using the right index. Now let’s run the below query.

Query 2:

cast2

You can see in the above plan when we use CAST on column of type int , it is showing a warning message and using wrong index.

It is safe to use cast function on column of type Date or Datetime.