CODEWITHSUNDEEP
sqlsql-serverdatetimeselect
Jesse
Jesse

Reputation: 1940

t-sql get record where date is exact match or within specified minutes

I'm looking to get a record where the date is either an exact match or within 5 minutes. 

SELECT @TripId = t.Id
        FROM Obd.TodaysTrip t WITH(NOLOCK)
        WHERE (t.DeviceId = @Id)
          AND (t.LastAccOnTime = @LastAccOnTime)

When I pass @LastAccOnTime I would like to also match on one single record that is at the most 5 minutes prior to the current greatest t.LastAccOnTime

Upvotes: 1

Views: 85

Answers (2)

DigiFriend
DigiFriend

Reputation: 1184

Can use DATEADD in this manner, to get the time 5 minutes ago:

AND (t.LastAccOnTime = @LastAccOnTime 
     OR t.LastAccOnTime BETWEEN DATEADD(minute, -5, @LastAccOnTime) AND @LastAccOnTime)

Upvotes: 2

Mureinik
Mureinik

Reputation: 312404

You could use the datediff function to check the difference between t.LastAccOnTime and @LastAccOnTime:

SELECT @TripId = t.Id
FROM   Obd.TodaysTrip t WITH(NOLOCK)
WHERE  (t.DeviceId = @Id) AND 
       (ABS(DATEDIFF(MINUTE, t.LastAccOnTime, @LastAccOnTime)) <= 5)

Upvotes: 2

Related Questions