On Fri, Dec 2, 2016 at 1:01 PM, Werner Kleiner <sqlitetes...@gmail.com>
wrote:
> @Darren,
>
> my "problem" is, that the both trailing zeros 00 after 1.5 are
> missing in sqlite. In MySQL the price is stored as 1.500
>
Do the printf transparently via a view in front of your table? --DD
C:\Users\DDevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (price number);
sqlite> insert into t values (1.5), (0.1), (2.2);
sqlite> create view v as select printf('%7.3f', price) as price from t;
sqlite> select * from v;
1.500
0.100
2.200
sqlite> select typeof(price), price from t;
real|1.5
real|0.1
real|2.2
sqlite> select typeof(price), price from v;
text| 1.500
text| 0.100
text| 2.200
sqlite> drop view v;
sqlite> create view v as select printf('%.3f', price) as price from t;
sqlite> select typeof(price), price from v;
text|1.500
text|0.100
text|2.200
sqlite> select sum(price) from v;
3.8
sqlite> select sum(price) from t;
3.8
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
