Search code examples
sqlsql-serverdatetimecomparison

SQL selecting datetime values which are 'close' to each other

I have a system that plans events at a certain time (plannedTime), when these events are played out the same table is updated with the row (playedTime), both are of type datetime, so comparison should be pretty straight forward. Doing it in an application wouldn't be a problem but is there an easy way of selecting the events that have a playedTime which is within 10 minutes of the plannedTime in one query?

I've tried:

SELECT * FROM eventlist
where playedTime LIKE plannedTime

However, that will just give me the events that are played at the exact same time as they were planned which is few if any.

My idea is to create a timespan between played- and plannedTime and if that does not exceed 10 minutes, include it in the result but I don't know where to start in an SQL Query.

Solution

  • I think you want time comparisons:

    SELECT el.*
FROM eventlist el
WHERE el.playedTime <= DATEADD(minute, 10, plannedTime) AND
      el.playedTime >= DATEADD(minute, -10, plannedTime);

    Note: This interprets "within 10 minutes" as being either before or after.