Joining by datetimefield SQL Server.

Answer 1

This is one way to do it:

select * from table_a, table_b
where table_a.sku = table_b.sku
and abs(datediff(second,table_a.datetime,table_b.datetime))<=3

Be careful, with big tables this kind of join can be very slow.

Answer 2

SELECT tbl1.*, tbl2.*
FROM tbl1, tbl2
WHERE ABS(DATEDIFF(second, tb1.date, tbl2.date)) <= 3

Answer 3

SELECT
  t1.id,
  t2.id
FROM
  t1
  INNER JOIN t2 ON ABS(DATEDIFF(ms, t1.datefield, t2.datefield)) <= 3000
WHERE
  ...

Beware, this will be slow. And probably not always right (as in: it will not always join records that should be joined because they belong together, it will of course always be technically correct).

EDIT:

Changed from ABS(DATEDIFF(ss, t1.datefield, t2.datefield)) <= 3 to the above because of @richardtallent's excellent observation in the comments.

Answer 4

Two ways spring into mind:

... on other.date between dateadd(second,-3,log.date) and dateadd(second,3,log.date)

and:

... on abs(datediff(millisecond, other.date, log.date)) <= 3000

Check which one gives you the best performance.

(Edit: Changed seconds to milliseconds in the second alternative, as richardtallent pointed out in a comment to a different answer.)

Answer 5

It's challenging to do it efficiently, because you end up querying using an expression rather than field values directly, and such queries don't use indexes well.

In particular, don't use DATEDIFF.

You might have a chance with

WHERE DATEADD(ms, date1, 3) < date2 AND DATEADD(ms, date1, -3) > date2

or

WHERE date2 BETWEEN DATEADD(ms, date1, 3) AND DATEADD(ms, date1, -3)

Note: It's usually possible to refactor the insertions to have exactly the same timestamp values if they derive from the same event or transaction (but then they would have some other common key and you would't need to do this.)

You could also store the original value at a lower resolution - say full second, or even minute - depending on your requirements, but then you'd still have the boundary problems.

Answer 6

Not tested, but this should work...

Select *
From tbl1
  inner join tbl2 on tbl1.id=tbl2.id and 
     Datediff(second, tbl1.dttime, tbl2.dttime) between -3 and 3

Answer 7

Can you depend on the order of the datetime values for rows that should match? For example, does the log entry always precede the entry into the other table? If so, modify one of the answers above to remove the ABS() function (taking special care of the order of the DATEDIFF parameters). This will help to prevent false matches.