SQLite format number with 2 decimal places always

I have some tables in a SQLite database that contains FLOAT column type, now i want to retrieve the value with a Query and format the float field always with 2 decimal places, so far i have written i query like this one :

SELECT ROUND(floatField,2) AS field FROM table 

this return a resultset like the the following :

3.56 ---- 2.4 ---- 4.78 ---- 3 

I want to format the result always with two decimal places, so :

3.56 ---- 2.40 ---- 4.78 ---- 3.00 

Is it possible to format always with 2 decimal places directly from the SQL Query ? How can it be done in SQLite ?

Thanks.

4 Answers

You can use printf as:

SELECT printf("%.2f", floatField) AS field FROM table; 

for example:

sqlite> SELECT printf("%.2f", floatField) AS field FROM mytable; 3.56 2.40 4.78 3.00 sqlite> 

On older versions of SQLite that don't have PRINTF available you can use ROUND.

For example...

SELECT ROUND(time_in_secs/1000.0/60.0, 2) || " mins" AS time, ROUND(thing_count/100.0, 2) || " %" AS percent FROM blah 

Due to the way sqlite stores numbers internally,

You probably want something like this

select case when substr(num,length(ROUND(floatField,2))-1,1) = "." then num || "0" else num end from table; 

u can solve it like this:

SELECT round(-4.535,2); 

-4.54