ms-sql rounding issue

[threepoints]

I am doing a query to get the contents of a price field, type=money in a ms-sql db (SQL2000). It returns the value but rounds to two decimal places. I need at least four to perform subsequent calculations.

$sql_result_trans = mssql_query($trans, $msconnection);
while ($row = mssql_fetch_array($sql_result_trans)) {
$trans_num = $row["transactionnumber"];
$item_id = $row["itemid"];
$price = $row["price"];
$qty = $row["quantity"];
echo "||$price...$qty||<br>";
}

Before I go to the extreme of chaning the field type of the database (it's a commercial product and doing that may cause other problems), I was hoping for some advice from this vast brain trust! Thanks.....3p

[bastien]

in the table to 4 places

hth

[threepoints]

if I view the table with Enterprise Manager I see the length of the "money" field is 8 (in the table design view) and several values in the table have 4 decimal places. My query doesn't appear to truncate the field but round it up (89.375 becomes 89.38). Do you think I would get in much trouble changing the field type from "money" to "decimal(10,6)" as I am not sure what problems this might cause elsewhere since the db is part of an off-the-shelf POS program?

Thanks for your help.

[tomhath]

You might want to see if the calculations are using two or four significant digits. This is from a Microsoft SQL Server Reference manual:

"SQL Server treats the value as a numeric with a scale of 4.
...
When monetary data is displayed, they are rounded up to the nearest cent."