5

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

Pac0
  • 21,465
  • 8
  • 65
  • 74
Atul R
  • 51
  • 1
  • 10
  • related : [way to extract from a DateTime value data without seconds](https://stackoverflow.com/questions/8896663/a-way-to-extract-from-a-datetime-value-data-without-seconds) – Pac0 Mar 15 '18 at 06:19

5 Answers5

7

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
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.

Pac0
  • 21,465
  • 8
  • 65
  • 74
1

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
Gaurang Dave
  • 3,956
  • 2
  • 15
  • 34
0
@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

Jcl
  • 27,696
  • 5
  • 61
  • 92
-1

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)

Drizzt
  • 19
  • 6