[AccessD] SQL datetime to Access date conversion

Hi Doug

This should be a non-issue. As Charlotte notes, SQL Server traditionally (SQL Server 2008 has a new DateTime2 data type with microsecond precision) saves time with a pseudo ms precision (rounded to 0, 3 or 7 ms or by 3.333 ms) while Access does have the implicit ability to store with a precision of a single millisecond. This, however,  is not displayed by default - all times are rounded to the second before display. 

Thus, if you aggregate a bunch of date/time values from SQL Server you may experience a rounding error, but to obtain errors counted in minutes you would need at least 12.000 records or values.
If this is the case, to play safe you could use the function DateTimeRound to round the times from SQL Server to the second:

<code>
Private Const clngSecondsPerDay       As Long = 24& * 60& * 60&

Public Function DateTimeRound( _
  ByVal datTime As Date) _
  As Date
  
' Returns datTime rounded off to the second by
' removing a millisecond portion.
  
  Call RoundSecondOff(datTime)
  
  DateTimeRound = datTime
  
End Function

Private Sub RoundSecondOff( _
  ByRef datDate As Date)
  
' Rounds off datDate to the second by
' removing a millisecond portion.

  Dim lngDate             As Long
  Dim lngTime             As Long
  Dim dblTime             As Double
  
  ' Get date part.
  lngDate = Fix(datDate)
  ' Get time part.
  dblTime = datDate - lngDate
  ' Round time part to the second.
  lngTime = Fix(dblTime * clngSecondsPerDay)
  ' Return date part and rounded time part.
  datDate = CVDate(lngDate + lngTime / clngSecondsPerDay)
  
End Sub
</code>

This is a snippet from the module for download here:

  http://www.devx.com/dbzone/Article/39046 

/gustav


>>> dbdoug at gmail.com 01-09-2009 00:27 >>>
Hello All:

I have a SQL 2005 table linked into an Access db.  I am using an update
query in Access to move some datetime fields from SQL Server to an Access
table.  It almost works - the dates are OK, but I am getting times which are
sometimes out by a couple of minutes: for instance the time part of one
field, 2:00:00 PM in SS, is changed to 1:58:07 PM in Access.

Does anyone have any suggestions about why this happens?

Thanks,
Doug Steele