Geek Answers Handbook

Decimal point problem with SQLite

I have a SQLite3 table with a column that has the format DECIMAL (7,2), but whenever I select rows with values ​​that do not have a non-zero 2nd decimal place (e.g. 3.00 or 3.10), the result always has zero (s ) missing (e.g. 3 or 3.1). Is there a way I can apply the formatting function in a SELECT statement to get the required 2dp? I tried ROUND (), but this has no effect. Otherwise, I will have to convert the resulting column values ​​to the required format for display (using Python in my case) every time I do a SELECT statement, which is a real pain.

I don't even mind if the result is a string instead of a number, if it has the correct number of decimal places.

Any help would be greatly appreciated.

Alan

+5
source share
1 answer

SQLite internally uses the IEEE binary floating point arithmetic, which is really not suitable for maintaining a certain number of decimal places. To get this type of decimal point processing, you need one of the following:

  • Fixed point math or
  • IEEE decimal floating point (rather unusual) or
  • Treating all as strings.

( ) - . - , . , , , ( Python, , ) .

+4

More articles:


All Articles