CODEWITHSUNDEEP
sqlt-sqlsql-server-2008datetime
divided
divided

Reputation: 1319

SQL: match time on several minutes

I'm trying to match datetimes from two different tables. Table A has a regular time (09:29:35). Table B has a rounded time (09:30).

The time from table B could match the time from table A, or it could match the minute before due to rounding.

How can I match these times? Or, how can I 'remove' the seconds from table A and round the minutes on that table as well?

Thanks for the help in advance!

Upvotes: 0

Views: 925

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44336

SELECT * FROM tableA a
JOIN tableb b
ON a.regulartime between dateadd(minute, -5, b.roundtime)
AND dateadd(minute, +5, b.roundtime)

Upvotes: 2

Andomar
Andomar

Reputation: 238206

You can remove the seconds with a cast:

convert(varchar(16), getdate(), 120)

This chops the date after the seconds, f.e. 2011-06-30 19:50 (16 characters long.) Adding or substracting minutes can be done with dateadd.

If you combine it in a join, you get something like:

select  *
from    TableA
join    TableB
on      convert(varchar(16), TableA.DateColumn, 120) between
        convert(varchar(16), dateadd(m,-1,TableB.DateColumn), 120)
        and convert(varchar(16), dateadd(m,1,TableB.DateColumn), 120)

Upvotes: 1

Related Questions