Storing as either an integer or real will let you do math on the returned
value. If you're looking for a pretty representation of the number, then that
should be done in your own application.
Alternatively though, you can explicitly query for the formatted text version
of your number using the printf() function.
Integer:
select printf('.03f', 1700 / 1000.0); returns the text string '1.700' (The .0
on the 1000.0 is important by the way)
Real:
select printf('.03f', 1.7); returns the text string '1.700'
So when getting the number for math just use
select basicprice from prices;
And when you're printing a pretty representation to the screen use either
select printf('.03f', basicprice) from prices;--if real
or
select printf('.03f', basicprice / 1000.0) from prices;--if integer
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Simon Slavin
Sent: Wednesday, November 30, 2016 11:34 AM
To: SQLite mailing list
Subject: Re: [sqlite] Datatype for prices (1,500)
On 30 Nov 2016, at 3:52pm, Werner Kleiner <sqlitetes...@gmail.com> wrote:
> <<Declare it as REAL.
>
> I tried this, but Inserting 1.700 is also stored as 1.7
>
> With TEXT I can store it like 1.700 but we want to calculate later
> with PHP or C#
If what you want is calculation-ready value, then storing it as REAL=1.7 is
correct.
If what you want is a print-ready value, then storing it as TEXT='1.700' is
correct.
There is no type which is good for both things.
Since most print routines already have formatting options which allow you to
specify the number of decimal points, it’s more convenient to store as a number
and worry about the text format only when you’re printing.
On the other hand, if you value the accuracy of a money calculation very
highly, then you should be storing an integer, not a REAL value. This means
you do not run the risk of storing a value which is not an exact amount of
money.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
