[AccessD] Access + SQL Server Date/Time Conclusions

[Gustav Brock]

Hi Paul

I tried to find some math behind the 0, 3, 7 rounding but gave up and fell back to a simple Choose expression in this function, that – if set to round to match DateTime of SQL Server – will return values that always will match exactly:

<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>

/gustav

Fra: Paul Wolstenholme <Paul.W at industrialcontrol.co.nz>
Sendt: 6. juli 2022 23:10
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Cc: Gustav Brock <gustav at cactus.dk>
Emne: Re: [AccessD] Access + SQL Server Date/Time Conclusions

Gustav,

I had not considered compatibility with older drivers.  Thanks for pointing that out.

The concept of non-uniform resolution and the measurements that best describe it was missing from my education.  My attempts to resolve this matter by looking for precise definitions of resolution in this context were totally thwarted by resolution being such an overused word.  About 3.333 ms and up to 4 ms and 3 or 4 ms are probably all correct.  I lack the resolve to look further.

Paul Wolstenholme


On Wed, 6 Jul 2022 at 19:57, Gustav Brock via AccessD <accessd at databaseadvisors.com<mailto:accessd at databaseadvisors.com>> wrote:
Hi Paul

I didn’t write 3.333 ms, I wrote about 3.333 ms as the exact value varies and isn’t that important; actually, it is the average.

DateTime is preferred among most Access developers, because it will cause zero troubles and requires no concerns. It even works with the native ”SQL Server” ODBC driver of Windows, should you be forced to work with that in some locked down corporate environment.
Also, if you don’t operate with early dates and millisecond precision, DateTime2 offers nothing extra.

/gustav