[AccessD] Losing 3ms on date insert?

[Ryan W]

Date is stored in a local table on the MS Access side

Then an insert into remoteTbl select * from localTbl is performed to move
it server side where some more processing happens a bit later.



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

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