CODEWITHSUNDEEP
sqlsql-serverdatetimeparameters
Fendec
Fendec

Reputation: 367

SQL - Retrieve all records that appear within X seconds either side of a specified records datetime field

When I store a record in my SQL table it is stored with an eventDateTime.

I want to return all records where their eventDateTime is X seconds either side of a specified records eventDateTime.

I have tried it but it just won't work for me, I am getting nowhere.

Example:

Give me all the records that have an eventDateTime in the 10 seconds before and after a supplied record eventDateTime.

Can this be done? All help is extremely appreciated!

Upvotes: 2

Views: 1654

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271191

You can do this using date logic. Here is one method:

select t.*
from t join
     t t2
     on t2.id = @id and
        t.datetime between dateadd(second, - @n, t2.datetime) and dateadd(second, @n, t2.datetime);

In this formulation, @id is the logic that represents the row you want to match, and @n is the number of seconds on either side.

Upvotes: 3

Related Questions