Compare dates in T-SQL, ignoring the time part

by

The most reasonable way to do this is to strip away the time portion of the datetime values and compare the results, and the best way to strip the time portion from a datetime is like this:

cast(current_timestamp as date)

I used to use and advocate a process that looked like one of the following two lines: 

cast(floor(cast(getdate() as float)) as datetime)
dateadd(dd,0, datediff(dd,0, getDate()))

But now that Sql Server has the Date type, which does not hold a time component, there is little reason to use either of those techniques.

One more thing to keep in mind is this will still bog down a query if you need to do it for two datetime values for every row in a where clause or join condition. If possible you want to factor this out somehow so it’s pre-computed as much as possible, for example using a view or computed column.

Finally, note the DATEDIFF function compares the number of boundaries crossed. This means the datediff in days between '2009-09-14 11:59:59' and '2009-09-15 00:00:01' is 1, even though only 2 seconds has elapsed, but the DATEDIFF in days between '2009-09-15 00:00:01' and '2009-09-15 11:59:59' is still zero, even though 86,398 seconds elapsed. It doesn’t really care at all about the time portion there, only the boundaries. Depending on what your query is trying to do, you might be able to use that to your advantage.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Get top 1 row of each group
  3. How can I do an UPDATE statement with JOIN in SQL Server?
  4. SQL update query using joins
  5. How to update two tables in one statement in SQL Server 2005?
  6. SQL query to select dates between two dates
  7. Convert Month Number to Month Name Function in SQL
  8. Create a date from day month and year with T-SQL
  9. Selecting COUNT(*) with DISTINCT
  10. How to return multiple values in one column (T-SQL)?
  11. Generate a resultset of incrementing dates in TSQL
  12. Getting result of dynamic SQL into a variable for sql-server
  13. Better techniques for trimming leading zeros in SQL Server?
  14. SQL – Subtracting a depleting value from rows
  15. Is it possible to select sql server data using column ordinal position
  16. SELECT FOR UPDATE with SQL Server
  17. How can a LEFT OUTER JOIN return more records than exist in the left table?
  18. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  19. Dynamic SQL to generate column names?
  20. Column name or number of supplied values does not match table definition
  21. How to split string using delimiter char using T-SQL?
  22. How to find consecutive rows based on the value of a column?
  23. OPENXML with xmlns:dt
  24. SQL server select distinct rows using most recent value only
  25. How to escape square brackets inside square brackets for field name
  26. Eliminate and reduce overlapping date ranges
  27. Is there a quick way to check if ANY column is NULL?
  28. Why do SQL Server Scalar-valued functions get slower?
  29. SQL query for parent-child chain
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment