How to compare two dates ignoring seconds in sql server

Question

I have two dates in SQL Server

@dt1 = 2018-03-15 11:12:10
@dt2 = 2018-03-15 11:12:45

I want (@dt1 = @dt2)

This condition should be true.

In short, I want to ignore seconds and only consider date and hours & minutes.

How can I do it in SQL Server??

Answer 1

All these answers rounding the values are ignoring that we can just ask for the difference in minutes between the two values and if it's 0, we know they occur within the same minute:

select CASE WHEN DATEDIFF(minute,@dt1,@dt2) = 0 THEN 'Equal' ELSE 'Not equal' END

This works because DATEDIFF counts transitions, which is often counter-intuitive to "human" interpretations - e.g. DATEDIFF(minute,'10:59:59','11:00:01') is going to return 1 rather than 0, despite the times only being 2 seconds apart - but this is exactly the interpretation you're seeking, where all smaller units within the datetime are ignored.

Answer 2

Compare datetimes both rounded down to the minute :

DATEDIFF(MINUTE, @d1, @d2)

This will be true if and only if the value differs by the minute.