What is the optimal way to compare dates in Microsoft SQL server

Question

I have a SQL datetime field in a very large table  It s indexed and needs to be queried   The problem is that SQL always stores the time component  even though it s always midnight   but the searches are to the day  rather than time   declare  dateVar datetime    2013-03-11   select t  DateColumn  from MyTable t where t  DateColumn    dateVar    Won t return anything  as the t  DateColumn  always includes a time component   My question is what is the best way round this   There seem to be two main groups of options    Create a second variable using dateadd and use a between     and or  gt       and      lt     Convert the t  DateColumn  into a date-only component - I think this will cause any indexes to be ignored    Both of these seem very messy - I don t really want to be making a range comparison or scan the table   Is there a better way   If one of these options is consistently optimal way then how and why

User · Answer

You could add a calculated column that includes only the date without the time   Between the two options  I d go with the BETWEEN operator because it s  cleaner  to me and should make better use of indexes   Comparing execution plans would seem to indicate that BETWEEN would be faster  however  in actual testing they performed the same

User · Answer

Converting to a DATE or using an open-ended date range in any case will yield the best performance  FYI  convert to date using an index are the best performers  More testing a different techniques in article  What is the most efficient way to trim time from datetime  Posted by Aaron Bertrand  From that article   DECLARE  dateVar datetime    19700204    -- Quickest when there is an index on t  DateColumn    -- because CONVERT can still use the index  SELECT t  DateColumn  FROM MyTable t WHERE   CONVERT DATE  t  DateColumn     CONVERT DATE   dateVar    -- Quicker when there is no index on t  DateColumn  DECLARE  dateEnd datetime   DATEADD DAY  1   dateVar   SELECT t  DateColumn   FROM MyTable t WHERE t  DateColumn   gt    dateVar AND        t  DateColumn   lt   dateEnd    Also from that article  using BETWEEN  DATEDIFF or CONVERT CHAR 8     are all slower

User · Answer

Here is an example   I ve an Order table with a DateTime field called OrderDate  I want to retrieve all orders where the order date is equals to 01 01 2006  there are next ways to do it   1  WHERE DateDiff dd  OrderDate   01 01 2006     0 2  WHERE Convert varchar 20   OrderDate  101     01 01 2006  3  WHERE Year OrderDate    2006 AND Month OrderDate    1 and Day OrderDate  1 4  WHERE OrderDate LIKE  01 01 2006   5  WHERE OrderDate  gt    01 01 2006   AND OrderDate  lt   01 02 2006    Is found here

User · Answer

Get items when the date is between fromdate and toDate   where  convert date  fromdate  103    lt    2016-07-26  and convert date  toDate  103      2016-07-26

[sql] What is the optimal way to compare dates in Microsoft SQL server?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to datetime

Examples related to query-optimization