[AccessD] Losing 3ms on date insert?

[Gustav Brock]

Hi Ryan

Maybe that static date isn't that static. 

How do run/call this code, and how was the date stored?

/gustav
________________________________________
Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af Ryan W <wrwehler at gmail.com>
Sendt: 21. november 2018 19:17
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Losing 3ms on date insert?

Huh, that's odd. I don't see why we're losing 3ms on a static date and
time, though. The date is 11/20/2018 16:00.. the first two entries are
a-ok, but subsequent ones get bonked down 3ms.   I'm expecting 'precise' to
the second, not millisecond in this case.


On Wed, Nov 21, 2018 at 12:16 PM Gustav Brock <gustav at cactus.dk> wrote:

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