[AccessD] Losing 3ms on date insert?

[James Button]

Firstly - Gravitate how? - well turn your screen on it's side to avoid that effect of gravitation
(snigger) 

However some of the following may be also associated with the problem - 

AFAIK 

VBA times are stored with less accuracy than Excel 
So it could be that the value is being truncated within the transmission from app to app 

IN VBA a date is an 8 bit floating point value as in  integer day and fractional hh:mm:ss  

Note   NO MILLISECONDS

but with VBA you get dates before December 30, 1899 using negative integers 
Standards (Ha! - ha ha ha ) for PC hardware is usually that numbers are stored to 15 significant digits  with about 300 "0"'s between the decimal point and that (up to 16) digit string.


In VBA there is CURRENCY datatype  that stores a value of up to 15 digits before the decimal point and up to 4 after it . 
Decimal is even more fun - as in different from Excel as Decimal allows up to 28 decimal places with the decimal place anywhere within those 28
BUT you have to use the CDEC function to convert the value into a 12byte storage block that is actually held under the Variant DIIM structure!

Also remember Boolean conversion from/to numeric equivalent is different in VBA and Excel.
In VBA there are
Integer from -32,768 up to +32,767 
Long which is -2,147,483,648 up to +2,147,483,647
Double -1.79769313486231E308 up to +1.79769313486232E308
And - for those with 64 bit OS and applications 
There is the LongLong VBA data type for 18, well, almost 19 digit numbers from -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.




Also re accuracy of hundreds of digits  
Way back before PC's, there were  machines that did not have storage arranged in 'words' of 4 8 or 16 bytes with each byte being used to hold an 8 bit character, 
But the systems memory was in individual 7 bit characters with extra bits associated with each character (1 or 2 of them) used to define the end of a 'word' or an 'item' or a 'record'
So addition and subtraction  was done on a 'character' mode working along the digits until a word ending bit was found, so a number could be almost all of memory (H200 systems)
There were also systems with 36 and systems with 48 bit words - just to really annoy those doing conversions to newer (and different manufacturer's) hardware.
Even more fun when a manufacturer moved their hardware from 7 bit data storage to 9 bit (e.g. mag-tapes) so the replacement hardware would not run the drives that read the old data storage media 
Hey - seen a 5¼ floppy disk recently, let alone an 8" or 11" one ?
OK - how about a 3" one?


And - NOT MANY PPEOPLE KNOW THIS 
Within VBA - Date formats 
c         means date (if value >=1) and then hh:mm:ss if a fractional part
oooo means monthname in the local format 
aaaa  means weekday in local format
w means weekday (Sunday is day 1)
ww is week of the year 
m is month, or if in {} or following h then minutes
n is minutes  - so you don't need to use m
q means quarter of the year
y means day of the year

and text  
one or more @ characters means the text string at this place in the string  and only as many characters as there are @'s
one or more & characters means the character at this place in the string  and only as many characters as there are &'s 
The output will be the exact number of & or @ in the string with the source characters dropped where format string includes spaces in a string of @ or &
spaces in an @ string meaning a space in the output and a space in an & string means closeup the result.

Prefix markers in the format string can be 
> means output uppercase
<means output lowercase
! means match to @ or & string from the left of the source rather than from the right
; as a suffix means that the following characters are to be used if the entry is void/null  or zero 
You can also use " bounded literals with @ or & or unbounded characters each preceded with a \ so the interpreter knows they are not formatting control 
 

And for those confused about log tables - the answer is as many as you want to pay for, 
Or if generating them yourself, It's up to you, your calculations, and your calculator's capability 
Or - the size of your slipstick and how good your vision was for aligning the marked values ! 

JimB 

Subject: Re: [AccessD] Losing 3ms on date insert?
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 


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

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.