[AccessD] Losing 3ms on date insert?

[Ryan W]

Date/Time in MS Access
DateTime in SQL Server

It IS coming from excel, formatted as mm/dd/yyyy hh:mm (custom format)

The dates are accurate in MS Access, but somehow we lose 3ms when pushing
the data to SQL Server (2008).  Oddly this problem seems to have only
cropped up semi recently.



On Wed, Nov 21, 2018 at 12:27 PM James Button via AccessD <
accessd at databaseadvisors.com> wrote:

> For that small amount I'd be looking at the internal formats used on the
> source
> & destination systems as well as the way the value is held in the
> transferring
> media
> Probably one of them is not holding enough decimal places (or allowing a
> big
> enough n in  2-n) in the storage of the value, so is rounding
> And, as it is only milliseconds, maybe round to the nearest second would
> deal
> with the visibility of the glitch.
>
> The date and time being held, by Excel as in 43424.6666666667
> And rounding it up to the 7th place  as in adding  0.00000003 to get
> 43424.66666667
> May be the reason for the extra 3 milliseconds being indicated
> While 43424.66666668 shows 4 milliseconds.
>
> And ... that is the perennial problem with the inexperienced use of
> computers
> Well, that and that the books and teachers rarely mention such things - so
> the
> first you know is when it bites you
> and only the fortunate get to notice it in their work before it becomes a
> public
> humiliation
>
> That - and the other major pain - people who do NOT check that cash values
> are
> not only shown as being in bankable portions of the currency - but are
> actually
> the same value as is being shown
> e.g. $1 split into thirds is 1/3 + 1/3 +1/3 and for the computer the total
> of
> 1/3 + 1/3 +1/3 does NOT = 1.00
> So - validation should be dealing with whole cents  when the checking has
> 0.99
> not = 1.00
> or the split  of the value needs to have the last portion being the base
> value
> less the sum of the other portions,
> or management have to specify some other process to deal with the many
> little
> bits.
> And the coder should consider if(abs(a-b)<0.000limit) rather than if a=b
> or if
> a-b=0.
>
> JimB
>
>
>
>
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Daniel
> Waters
> Sent: Wednesday, November 21, 2018 5:39 PM
> To: Access Developers discussion and problem solving
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Losing 3ms on date insert?
>
> That is a puzzle - I have no answer.  :-(
>
> Dan
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Ryan W
> Sent: November 21, 2018 11:33
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Losing 3ms on date insert?
>
> No SQL Server is on a VM... but these are 'set' dates, not using GETDATE()
> or DATE() or a function where a tiny time discrepancy like that might show
> up.
>
> So the source table has 11/20/2018 16:00 as the date, but the destination
> table, after the first two rows gets rolled back by 3ms for some reason.
>
>
>
> On Wed, Nov 21, 2018 at 10:54 AM Daniel Waters <df.waters at outlook.com>
> wrote:
>
> > Is the Access file and the SQL Server on the same PC?  Or on a PC where
> > SQL Server is on a Server?
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > Ryan W
> > Sent: November 21, 2018 10:47
> > To: Access Developers discussion and problem solving
> > Subject: [AccessD] Losing 3ms on date insert?
> >
> > Has anyone seen an issue where:
> >
> > INSERT INTO FOO SELECT * FROM BAR
> >
> > Where FOO and BAR both contain a date column, but when inserted in from
> MS
> > Access to SQL the date loses 3ms?
> >
> >
> > The first two rows, proper date.. following rows -3ms ?
> >
> > https://i.imgur.com/IYsKCbS.png (Screencap)
> > --
> > 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
> >
> --
> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>