OS: Mac M3
Java Version: openjdk11
Tomcat Version: not sure using commandbox
Lucee Version: 5.3.7.48
**Mysql 8.0.42
Hi all,
I just noticed that my decimals are being rounded when using cfquery to pull a column from the database that is defined as DECIMAL(10,2)
The first ten rows from the db
‘9833386705210’,‘9.00’
‘9833386737978’,‘9.00’
‘9833386770746’,‘11.00’
‘9833387032890’,‘9.00’
‘9833387196730’,‘9.00’
‘9833387262266’,‘9.00’
‘9833387295034’,‘13.00’
‘9833387393338’,‘9.00’
‘9833387524410’,‘9.00’
‘9833387557178’,‘13.00’
and the results of my cfquery
is there any way to prevent this, I am exporting the data to a csv file and I would like to have the decimal places in tact without having to use numberformat.
Cheers,
Gary
Dump( QueryExecute( "SELECT productid, FORMAT( price, 2 ) AS price FROM products" ) )
nice, sql numberFormat!
background, as Lucee uses java types, which don’t store trailing zeros, hence the need for formatting
<cfscript>
x = "1.0";
dump(x);
dump(toNumeric(x));
</cfscript>
this all depends on the type “price” has, if it is varchar is is read in as string and should show “9.00”, but if it is a number type it is read in as double (in Lucee 5) and SHOWN as “9”, to keep the format “0.00” you need to read it as string.
There is a difference to ACF on this, ACF reads in all simple types (string,numbers,boolean) as Strings, what may preserve the looks of it, but makes it A LOT slower, specially when you wanna do any kind of operation with them.
3 Likes
wouldnt it be cool if we had a currency data type in lucee, localised and all with currency symbols and decimal places
#ooohhhh…
what you mean a type which doesn’t do weird floating point things when working with dollars and cents? absolute crazy talk!!! kinda like it
1 Like
