Compare two DATETIME only by date not time in SQL Server 2008

Question

I have two tables (bank statement and client requests) where column [date] is type of DATETIME2(0).

I have to compare two records only by theirs Date parts (day+month+year), discarding Time parts (hours+minutes+seconds).

How can I dot that? I tried CAST and CONVERT but it didn't helped.

Update:

1. Marc_s's answer works

2. DATEADD(dd, 0, DATEDIFF(dd, 0, [field1])) = DATEADD(dd, 0, DATEDIFF(dd, 0, [field2])) -- this also works - it withdraws date parts from DATETIME and compares

Answer 1

Use the CAST to the new DATE data type in SQL Server 2008 to compare just the date portion:

IF CAST(DateField1 AS DATE) = CAST(DateField2 AS DATE)

Answer 2

A small drawback in Marc's answer is that both datefields have been typecast, meaning you'll be unable to leverage any indexes.

So, if there is a need to write a query that can benefit from an index on a date field, then the following (rather convoluted) approach is necessary.

• The indexed datefield (call it DF1) must be untouched by any kind of function.
• So you have to compare DF1 to the full range of datetime values for the day of DF2.
• That is from the date-part of DF2, to the date-part of the day after DF2.
• I.e. (DF1 >= CAST(DF2 AS DATE)) AND (DF1 < DATEADD(dd, 1, CAST(DF2 AS DATE)))
• NOTE: It is very important that the comparison is >= (equality allowed) to the date of DF2, and (strictly) < the day after DF2. Also the BETWEEN operator doesn't work because it permits equality on both sides.

PS: Another means of extracting the date only (in older versions of SQL Server) is to use a trick of how the date is represented internally.

• Cast the date as a float.
• Truncate the fractional part
• Cast the value back to a datetime
• I.e. CAST(FLOOR(CAST(DF2 AS FLOAT)) AS DATETIME)