Dated and date announcement

Question

What is the difference between the following two logical conditions (both CreationDate - DateTime values)?

and abs(datediff(hour, a.CreationDate, e.CreationDate)) < 12 

and

  and e.CreationDate > dateadd(hour, -12, a.CreationDate) and e.CreationDate < dateadd(hour, 12, a.CreationDate) 

(yes, there is a difference - the change gives different results, but I do not see that)

Background

Recently, a question has appeared on Meta.se about curator icons and 12-hour editing windows. This question inspired me to make a copy of the SQL query that I wrote earlier, and make new editing windows the size of the "variable" size of no more than 12 hours on each side. Essentially, I changed the following code:

  and abs(datediff(hour, a.CreationDate, e.CreationDate)) < 12 

:

  and e.CreationDate > dateadd(hour, -12, a.CreationDate) and e.CreationDate < dateadd(hour, 12, a.CreationDate) 

(except 12 values ​​in the second expression are variables).

These two queries are in the Stack Exchange Data Explorer: the original and copied

Queries give slightly different results, though ... and I really scratch my head about why. I also cannot decide which query result is correct. I believe that this should lead to something in dated logic ... does it “spin” in an hour or something else, so that it has slightly less results?

DateDiff:

DateAdd:

Answer 1

DateDiff counts border crossings, and DateAdd does simple arithmetic.

For example, the first request will consider 12 boundaries between 00:59 and 12:01 and, thus, excludes this difference, but the second request will take it no more than 12 hours. This makes the second request “correct” and the first “wrong”.