Number format not preserved SQLITE


this is my issue: I write real numbers to a sqlite db where the column is specified as REAL. Let’s say I write 0.95. When I read it back it is displayed as 9.49999999999e-1. How to keep the original format when writing/reading?


use str or format with a formatting mask to round it.

SQLite stored doubles here which are rounded to 15 digits.
And some numbers can’t really be represented.

That IS the original value. Doubles can only hold the exact representation of a very few values. Mostly you have an approximation. Round or format it for display.

Thank you both for the answer. I was thinking to round it but the problem is that in general I don’t know how many decimal digits the original number has…Any suggestion?


you could store them as Int64 multiplied by 1000. That is what currency datatype does.

yes but unless I am missing something I still need to know a priori how many decimal digits my original number had.

If I read from the DB 1.890000000001e+1 my original number was 18.9 or 18.90 or 18.900? Please note that those numbers are not equivalent (in my case) as 18.900 reflects a higher measurement precision than 18.9.

you need to store them as text “18.900” or store precision somewhere in an extra field.

You can round to a large number of decimals, like format(n, "#.##########"). That will resolve all the trailing “99999999”. However, that doesn’t restore any trailing zeros, if they are needed. But, then, you would already know how many you needed anyway.

dim s as string = format(.94999999999999, "#.##########")   // yields ".95"

Currency have 4 decimal digits precision. So, V = N*10000. :slight_smile:

Floor(3.1415 * 10000) = Int64(31415)
Floor(3.1415 * 1000) = Int64(3141)

better round.

Most cases, yes, you are right. Just wanted to truncate for better understanding.