There's no way to force SQLite to return exactly 2 decimal places for
you. You have to do it in your application or if you really-really
need to do it in sql you can do it like this (assuming you need column
col from table tab):
select '$'||case
when length(col) = 1
then '0.0'||col
when length(col) = 2
then '0.'||col
else
substr(col, 1, length(col) - 2)||'.'||substr(col, length(col) - 1)
end as col
from (select cast(round(col * 100) as text) as col from tab)
But I wouldn't do that if I were you. ;-)
Pavel
On Tue, Nov 10, 2009 at 1:17 PM, Peter Haworth <p...@mollysrevenge.com> wrote:
> I have a column defined with a type of FLOAT, which I believe is
> treated as REAL by SQLite. When selecting that column, I would like
> it to be returned with a leading "$" sign and always have a decimal
> point and two numbers after the decimal point. I can use
> concatenation to get the "$" sign in there but have not been able to
> enforce the inclusion of a decimal point and two decimal places.
> Whole numbers are returned with no decimal point and no decimal
> places. Numbers where the second decimal place would be a zero are
> returned with the decimal point but only one decimal place.
>
> I tried using the round function specifying 2 decimal places but this
> does not affect the output. I have also tried using CAST to force the
> column into various other types but that also does not affect the
> output.
>
> Web searches suggest that the CONVERT function is available in other
> SQL implementations but I don't see that anywhere in the SQLite
> documentation. Is there a way to do this or should I plan on handling
> it within the application?
>
> Thanks
>
> Pete Haworth
>
>
>
>
>
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
