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

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

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

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

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

[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