CODEWITHSUNDEEP
sql-servert-sqldatetime
Atul R
Atul R

Reputation: 51

How to compare two dates ignoring seconds in sql server

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??

Upvotes: 5

Views: 5734

Answers (6)

sodeh
sodeh

Reputation: 1

declare @dt1 datetime = '2018-03-15 11:12:10';
declare @dt2 datetime = '2018-03-15 11:12:45';

if convert(varchar,@dt1,0) = convert(varchar,@dt2,0)
print 'match';
else
print 'no match';

Upvotes: 0

Drizzt
Drizzt

Reputation: 19

Here is your solution: How can I truncate a datetime in SQL Server?

In short it depends on your version. In SQL server 2008 there is cast(getDate as date)

Upvotes: -1

Pac0
Pac0

Reputation: 23174

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.

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

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.

Upvotes: 7

Jcl
Jcl

Reputation: 28272

@dt1withoutsecs = DATETIMEFROMPARTS(year(@dt1), month(@dt1), day(@dt1), DATEPART(hh,@dt1), DATEPART(mi,@dt1), 0, 0)
@dt2withoutsecs = DATETIMEFROMPARTS(year(@dt2), month(@dt2), day(@dt2), DATEPART(hh,@dt2), DATEPART(mi,@dt2), 0, 0)

You should be able to compare those two

Edit: I went and actually tested it (sqlfiddle didn't want to work today):

test

Upvotes: 2

Gaurang Dave
Gaurang Dave

Reputation: 4046

This should work for you.

@dt1 = SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, yourcolumn), 0)
@dt2 = SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, yourcolumn), 0)

IF @dt1 = @dt2
BEGIN

END

Upvotes: 2

Related Questions