[AccessD] Losing 3ms on date insert?

[Gustav Brock]

Hi Ryan

That is due to the limited resolution of data type DateTime of SQL Server. Use DateTime2 if you want a precise recording.

I've even created a function to exactly simulate that low resolution given input having a millisecond part (which VBA and Access is able to handle if you are not aware). It's all in the in-line comments:

<code>
' Returns Date1 rounded to the nearest millisecond approximately by 4/5.
' The dividing point for up/down rounding may vary between 0.3 and 0.7ms
' due to the limited resolution of data type Double.
'
' If RoundSqlServer is True, milliseconds are rounded by 3.333ms to match
' the rounding of the Datetime data type of SQL Server - to 0, 3 or 7 as the
' least significant digit:
'
' Msec SqlServer
'   0    0
'   1    0
'   2    3
'   3    3
'   4    3
'   5    7
'   6    7
'   7    7
'   8    7
'   9   10
'  10   10
'  11   10
'  12   13
'  13   13
'  14   13
'  15   17
'  16   17
'  17   17
'  18   17
'  19   20
' ...
' 990  990
' 991  990
' 992  993
' 993  993
' 994  993
' 995  997
' 996  997
' 997  997
' 998  997
' 999 1000
'
' If RoundSqlServer is True and if RoundSecondUp is True, 999ms will be
' rounded up to 1000ms - the next second - which may not be what you wish.
' If RoundSecondUp is False, 999ms will be rounded down to 997ms:
'
' 994  993
' 995  997
' 996  997
' 997  997
' 998  997
' 999  997
'
' If RoundSqlServer is False, RoundSecondUp is ignored.
'
' 2016-09-14. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateRoundMillisecond( _
    ByVal Date1 As Date, _
    Optional RoundSqlServer As Boolean, _
    Optional RoundSecondUp As Boolean) _
    As Date
  
    Dim Milliseconds    As Integer
    Dim MsecValue       As Date
    Dim Result          As Date
  
    ' Retrieve the millisecond part of Date1.
    Milliseconds = Millisecond(Date1)
    If RoundSqlServer = True Then
        ' Perform special rounding to match data type datetime of SQL Server.
        Milliseconds = (Milliseconds \ 10) * 10 + Choose(Milliseconds Mod 10 + 1, 0, 0, 3, 3, 3, 7, 7, 7, 7, 10)
        If RoundSecondUp = False Then
            If Milliseconds = 1000 Then
                Milliseconds = 997
            End If
        End If
    End If
    
    ' Round Date1 down to the second.
    Call RoundOffMilliseconds(Date1)
    ' Get milliseconds as date value.
    MsecValue = MsecSerial(Milliseconds)
    ' Add milliseconds to rounded date.
    Result = DateFromTimespan(DateToTimespan(Date1) + DateToTimespan(MsecValue))
  
    DateRoundMillisecond = Result
  
End Function
</code>

As you can see, a couple of helper functions are used - their names should indicate their doings. If you are interested, I can post these as well.

/gustav