Table scan or Unexpected output due to improper where condition applied for DateTime filter by serverku

When you are using date filter with queries, like to find the records from tables for the particular year/month, we are approaching some extra data type conversion on fields. Below are some examples.

Should not use,

 
-- #1. : This cause to table scan
SELECT *  
FROM OrderDetails
WHERE datepart(month,Orderdate) =?
AND datepart(year,Orderdate) = ?

-- #2. : This cause to unexpected output 
SELECT * 
FROM OrderDetails
WHERE convert(varchar,OrderDate,112) between
'20110101' and '20110131' 

Should use,

 
-- #1.
SELECT * 
FROM OrderDetails
WHERE convert(varchar,Orderdate,112) >= '20110101' 
AND convert(varchar,Orderdate,112) < '20110201' -- EndDate + 1 

-- #2.
SELECT * 
FROM OrderDetails
WHERE cast (OrderDate as datetime) BETWEEN
 '2011-01-01 00:00:00.000' AND '2011-01-31 23:59:59.900'

Create table with sample records or use existing table which has datetime data type field, Run query above and check the results with execution plan. Hope you like it.