Compare DATETIME and DATE ignoring time portion

Question

I have two tables 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 do that?

user_2597706 · Answer

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

user_2794452 · Answer

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