Compare DATETIME and DATE ignoring time portion

Question

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)

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)

Though I upvoted the answer marked as correct. I wanted to touch on a few things for anyone stumbling upon this.

In general, if you're filtering specifically on Date values alone. Microsoft recommends using the language neutral format of ymd or y-m-d.

Note that the form '2007-02-12' is considered language-neutral only for the data types DATE, DATETIME2, and DATETIMEOFFSET.

To do a date comparison using the aforementioned approach is simple. Consider the following, contrived example.

--112 is ISO format 'YYYYMMDD'
declare @filterDate char(8) = CONVERT(char(8), GETDATE(), 112)

select 
    * 
from 
    Sales.Orders
where
    CONVERT(char(8), OrderDate, 112) = @filterDate

In a perfect world, performing any manipulation to the filtered column should be avoided because this can prevent SQL Server from using indexes efficiently. That said, if the data you're storing is only ever concerned with the date and not time, consider storing as DATETIME with midnight as the time. Because:

When SQL Server converts the literal to the filtered column’s type, it assumes midnight when a time part isn’t indicated. If you want such a filter to return all rows from the specified date, you need to ensure that you store all values with midnight as the time.

Thus, assuming you are only concerned with date, and store your data as such. The above query can be simplified to:

--112 is ISO format 'YYYYMMDD'
declare @filterDate char(8) = CONVERT(char(8), GETDATE(), 112)

select 
    * 
from 
    Sales.Orders
where
    OrderDate = @filterDate

You can try this one

CONVERT(DATE, GETDATE()) = CONVERT(DATE,'2017-11-16 21:57:20.000')

I test that for MS SQL 2014 by following code

select case when CONVERT(DATE, GETDATE()) = CONVERT(DATE,'2017-11-16 21:57:20.000') then 'ok'
            else '' end

You may use DateDiff and compare by day.

DateDiff(dd,@date1,@date2) > 0

It means @date2 > @date1

For example :

select DateDiff(dd, '01/01/2021 10:20:00', '02/01/2021 10:20:00') 

has the result : 1