[AccessD] Access + SQL Server Date/Time Conclusions

[Martin]

I have always enjoyed your code and descriptions of this stuff over the
years Gustav.

Martin

On Thu, 7 Jul 2022, 08:41 Gustav Brock via AccessD, <
accessd at databaseadvisors.com> wrote:

> 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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>