Date Search in Mssql

Date searches

Date searches are sometimes a little tricky to perform in the database; for instance, I have seen numerous situations where date functions are used for date searches. In the following example, the sales records are being retrieved from the CustomerSaleMain table for August 04, 2012:

SELECT CSMId 
 FROM Sales.CustomerSaleMain
 WHERE
      MONTH(CreatedDateTime) = 8 AND
      YEAR(CreatedDateTime) = 2012 AND
      DAY(CreatedDateTime) = 04

The functions in the WHERE clause will cause SQL Server to perform the function on every row being searched, which means the index will not be used. This is why I discourage the returns values from functions to be used as criteria in queries. The following code shows how you can rewrite the statement so that an index is used, and the results are returned in a much quicker fashion.

SELECT CSMId 
 FROM Sales.CustomerSaleMain
 WHERE

      CreatedDateTime>= '8/15/2012' AND
      CreatedDateTime< ‘8/16/2012