How to test two datetimes for equality in T-SQL (ignoring their time components)?

Question

How can I test two datetimes (not including their time components) for equality?

Answer 1

Your best bet would be to use DATEDIFF

For example to only compare the months:

SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

This is the best way to do comparisons and determine the differences based on your exact need for the query your doing. It even goes down to milisecond.

Answer 2

This should work:

SELECT * FROM MyTable 
WHERE floor(convert(real,date1))=floor(convert(real,date2))

Answer 3

DATEDIFF(day, rem.DueDate , GETDATE()) = 0

Answer 4

To test if the two dates are equal, ignoring the time component:

SELECT DATEDIFF(day, @first, @second) = 0

To test if the two times are equal, ignoring the date component:

SELECT DATEADD(day, -DATEDIFF(day, 0, @first), @first) =
    DATEADD(day, -DATEDIFF(day, 0, @second), @second)

Answer 5

SELECT * FROM Invoice 
WHERE 
    CONVERT(DATETIME, CONVERT(VARCHAR,InvoiceDate,101)) 
  = CONVERT(DATETIME, CONVERT(VARCHAR,DateDelivered,101))

[EDIT]

Alternatively, instead of using typecasting, you can use this one, which is more efficient:

SELECT * FROM Invoice 
WHERE 
    DATEADD(YEAR, DATEDIFF(YEAR, 0, InvoiceDate), 0)
  = DATEADD(YEAR, DATEDIFF(YEAR, 0, DateDelivered), 0)

But still the code above resorts to table scan, and it's also a bit unfortunate that MSSQL don't have a single function to strip time from a DATETIME column, so if you are using MSSQL 2000 and up, just make a formula column, use the dateadd datediff expression above, then compare on that column.

And here are some ways you can improve your query efficiency(e.g. by avoiding table scan) on datetime columns: http://www.ideaexcursion.com/2009/02/17/efficiently-query-the-date-in-datetime/