MS SQL Join on dateTime

Question

Hi I have a query that joins two tables. The join is done on several columns, including a dateTime column. The problem that I have at the moment is that the one dateTime column stores milliseconds whereas the other table is populated from a file which does not contain milliseconds Because of this the join will never return results.

Is there any way via SQl to ignore the milliseconds?

Answer 1

If aTable.date contains millisenconds and bTable.date does not, to compare them:

... WHERE convert(varchar, aTable.date, 120) = bTable.date

converting datetime to varchar with arg2=120 produce this format: ODBC canonical yyyy-mm-dd hh:mi:ss

Answer 2

This still has a function but avoid strings and conversions

WHERE
    aTable.date >= bTable.date
    AND
    aTable.date < DATEADD(second, 1, bTable.date)