CODEWITHSUNDEEP
sqlsql-serverdatetimems-access
Nat
Nat

Reputation: 345

Join on DateTime sometimes fails between Access and SQL Server

I'm trying to join two tables in Access 2013 on date/time fields. One table is a local Access table, the other is a linked table from SQL Server 2012. The inner join only returns about half the values it should (both tables are originally populated from the same native Access table).

SELECT sample.*, r.*
FROM tblWQResultStaging_SingleParam AS r 
INNER JOIN dbo_tblWQSample AS sample 
ON (r.datetime_utc = sample.sample_datetime);

Sample data for tblWQResultStaging_SingleParam (this is the Access native table; data formatted per Access):

datetime_utc
07/30/91 6:50:00 PM
03/24/92 6:15:00 PM
01/29/91 9:30:00 PM
03/26/91 9:10:00 PM

Sample data from dbo_tblWQSample (this is the SQL Server linked table; formatted per Access):

sample_datetime
01/29/91 9:30:00 PM
03/26/91 9:10:00 PM
07/30/91 6:50:00 PM
03/24/92 6:15:00 PM

Outcome of query above:

sample_datetime
01/29/91 9:30:00 PM
03/26/91 9:10:00 PM

I've tried multiple SQL Server date/time field types - datetime, datetime2(7), datetime2(0), smalldatetime - and none of them work, despite refreshing the linked tables after changing the field type & deleting/reloading the data.

The best guess I've had so far is that rounding issues are causing some, but not all, dates to be loaded to SQL Server with different values from the original Access data, but I can't tell for sure.

Upvotes: 3

Views: 772

Answers (1)

Erik A
Erik A

Reputation: 32642

In Access, dates are actually doubles, and can have more detailed time information than visible stored (e.g. milliseconds)

When comparing dates in Access with dates in SQL server, you should use the DateDiff function and check that the difference is 0 seconds:

SELECT sample.*, r.*
FROM tblWQResultStaging_SingleParam AS r 
INNER JOIN dbo_tblWQSample AS sample 
ON (DateDiff("s", r.datetime_utc, sample.sample_datetime) = 0);

Upvotes: 2

Related Questions