Rounding problems

I have rounding problems when editing or inserting a new record in float type fields.
e.g. I have a cursor running an agrregate SQL statement. I have a calculated field Sum(DFactor*Cost). DFactor gets values -1,1 and values of Cost in the table have 2 digits. I get these values in a variable e.g. @.FCost. Then I round @.FCost=Round(@.FCost,2).
When I try to inert this value to a new record again I'using Round(@.FCost,2).
However in a lot of records a lot of digits are stored.
I have the same probelm when trying to insert values from MSAccess by ODBC. Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.
What is the best practise in order to solve this problem?
Regards,
ManolisIf you are using a FLOAT column to store data of type MONEY, that's a problem. If you are using a FLOAT column to store data of type DECIMAL (x, 2), that's also a problem. Is your underlying problem one of datatype, not actually rounding?

-PatP|||Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.The result will have decimals. You need this: CLng(@.FCost*100/100)

Rounding Modes and .NET Runtime Consistency

We have encountered something of a consistency issue in doing decimal type rounding between SQL Server 2005 and the .NET 2.0 runtime.

SQL Server seems to be using round to zero for Decimal and Money values. However, the .NET runtime, by default, uses round to even (also called 'Bankers Rounding'.) We are therefore stuck between a number of unpaletable options. We can:

Change every instance of rounding in our .NET codebase to use the overloaded rounding to force midpoint rounding.

Add a .NET extension to the database and call it in place of the built-in ROUND function.

Write our own banker's rounding function in T-SQL and call it.

Find a setting in either .NET or SQL Server to change the default rounding mode.

The first three all seem pretty nasty, though the middle two are probably the easiest within our (relatively speaking) C# heavy codebase. I seem to have pretty well eliminated finding a .NET setting (though I may have missed something.)

Is there an appropriate setting in SQL Server to do this? As yet I have not found one.

Andrew Raymond
Mitchell 1
Andrew.Raymond@.mitchell1.com

I don't believe there is any setting available to change the rounding scheme used in SQL Server, at least I can't remember ever having seen one...

/Kenneth

round money data type 2 decimal

How can I execute a query that will round a money data
type field to 2 decimal places. I have:
SELECT
AmountReceipt
FROM
InvoiceReceipt
WHERE
InvoiceID = 'xxxxxx'
I need to return 3099.93 instead of 3099.9300 because I
need to compare it to something else that has already
been rounded out.
TIA,
Vicyou're example may not be the best. The two numbers you use are in fact the
same. How do you want rounding to happen and how did you round the other
number? You could cast the value as decimal with a scale of 2. See Books
Online...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Vic" <vduran@.specpro-inc.com> wrote in message
news:12f0201c411fd$547ddfb0$a401280a@.phx
.gbl...
> How can I execute a query that will round a money data
> type field to 2 decimal places. I have:
> SELECT
> AmountReceipt
> FROM
> InvoiceReceipt
> WHERE
> InvoiceID = 'xxxxxx'
> I need to return 3099.93 instead of 3099.9300 because I
> need to compare it to something else that has already
> been rounded out.
> TIA,
> Vic