[AccessD] Losing 3ms on date insert?

[Jim Lawrence]

I wonder, if the number could be stored as just a value without a decimal point. Many years ago when working with the AcPac accounting package, I found they never stored decimal numbers because of the round off errors. They would store a number as a whole number and then store a mantissa value. So a number would store like 12345698123 and a mantissa value of 9 and when the value was displayed it showed 12.345698123.

Is that a possible solution?

Also, in my much younger years I used Fortran and it would have number values of a hundred of more decimal places. I do not remember any round off errors. Here is a link to an online Fortran editor: https://www.jdoodle.com/execute-fortran-online 

There is a full open source version of Fortran (http://gcc.gnu.org/fortran/) and its compiler...download it, run it and use it. ;-)

...And then of course, and I am really dating myself, there is log tables and log functions. They were basically used to eliminate the need for decimal numbers, multiplying and dividing. No round off or number storage errors.
 
Jim 

----- Original Message -----
From: "Gustav Brock" <gustav at cactus.dk>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Monday, November 26, 2018 7:21:58 AM
Subject: Re: [AccessD] Losing 3ms on date insert?

Hi Ryan

The original value is exactly 2018-11-20 16:00:00.000.
The gravitation is exactly 5ms.

It will, when stored in SQL Server using DateTime be rounded to:

43424.6666666319

which is - as you have noted - 3ms off.

However, the 5ms cannot even be caused by some rounding, so there must be something in between, we don't know of.

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces at databaseadvisors.com> På vegne af Ryan W
Sendt: 26. november 2018 16:01
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: Re: [AccessD] Losing 3ms on date insert?

Ok looking at this again the dates being stored in excel are start off as:
43424.6666666667


but then gravitate to:

43424.6666666088

I'm not sure I understand why though, they're all being input as 11/20/2018
16:00 and then copied down the row.





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