Cfquery rounding decimals

dev support
1

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

image
image560×690 20.9 KB

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

2 
Dump( QueryExecute( "SELECT productid, FORMAT( price, 2 ) AS price FROM products" ) )

Screenshot 2025-05-09 at 10-38-49

3

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>

image
image796×174 5.95 KB

4

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.

1 Like