How to compare datetime with only date in SQL Server

by

DON’T be tempted to do things like this:

Select * from [User] U where convert(varchar(10),U.DateCreated, 120) = '2014-02-07'

This is a better way:

Select * from [User] U 
where U.DateCreated >= '2014-02-07' and U.DateCreated < dateadd(day,1,'2014-02-07')

see: What does the word “SARGable” really mean?

EDIT +
There are 2 fundamental reasons for avoiding use of functions on data in the where clause (or in join conditions).

  1. In most cases using a function on data to filter or join removes the ability of the optimizer to access an index on that field, hence making the query slower (or more “costly”)
  2. The other is, for every row of data involved there is at least one calculation being performed. That could be adding hundreds, thousands or many millions of calculations to the query so that we can compare to a single criteria like 2014-02-07. It is far more efficient to alter the criteria to suit the data instead.

“Amending the criteria to suit the data” is my way of describing “use SARGABLE predicates”

And do not use between either.

the best practice with date and time ranges is to avoid BETWEEN and to
always use the form:

WHERE col >= ‘20120101’ AND col < ‘20120201’ This form works with all
types and all precisions, regardless of whether the time part is
applicable.

http://sqlmag.com/t-sql/t-sql-best-practices-part-2 (Itzik Ben-Gan)

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to return only the Date from a SQL Server DateTime datatype
  3. Best approach to remove time part of datetime in SQL Server
  4. DateTime2 vs DateTime in SQL Server
  5. SQL query to select dates between two dates
  6. How can I select the first day of a month in SQL?
  7. How can I convert bigint (UNIX timestamp) to datetime in SQL Server?
  8. Getting Dates between a range of dates
  9. Select distinct record from table and perform Sum of column (Pallats, Gross) of a duplicate row. And show that duplicate rows once [closed]
  10. When should I use CROSS APPLY over INNER JOIN?
  11. SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, =
  12. How to split a single column values to multiple column values?
  13. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  14. T-SQL: Round to nearest 15 minute interval
  15. When no ‘Order by’ is specified, what order does a query choose for your record set?
  16. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  17. How do I create a foreign key in SQL Server?
  18. How do I group on continuous ranges
  19. join comma delimited data column
  20. Need to list all triggers in SQL Server database with table name and table’s schema
  21. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  22. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  23. Best equivalent for IsInteger in SQL Server
  24. Store multiple bit values in a single table column
  25. SQL Query – Concatenating Results into One String [duplicate]
  26. T-SQL Skip Take Stored Procedure
  27. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  28. Compare dates in T-SQL, ignoring the time part
  29. How do I compare two columns for equality in SQL Server?
  30. SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns

Leave a Comment