# Thread: Help withrounding money to 2 decimal places?

1. Registered User
Join Date
Feb 2004
Posts
42

## Unanswered: Help withrounding money to 2 decimal places?

I have a table with a money field that had previously been running calculation and storing the data into the database's money field. Since this field supports 4 decimal places, it was storing 4 decimal places worth of data. I have since cleaned up my insert routine to round everything up to two decimal places and it only inserts the rounded values. I now have to go back and update the old data with the two decimal place rule. How would I go about doing this?

OLD---------------------------NEW
15.1456 ================ 15.15
4.1328 ================== 4.13
5.16 =================== 5.16

2. The SQL Apostle
Join Date
Jul 2003
Location
The Dark Planet
Posts
1,401
How about using this function ... http://thedailywtf.com/archive/2004/10/25/2882.aspx

3. Registered User
Join Date
Feb 2004
Posts
42
Nevermind, this was extremely easy ... perhaps I should have read the BOL first!

Update tblName
Set fldName = round(fldName, 2)

4. Registered User
Join Date
Feb 2004
Posts
42
Originally Posted by Enigma
How about using this function ... http://thedailywtf.com/archive/2004/10/25/2882.aspx
This isn't help. Granted, the question has an elementary solution; you could have simply stated such and gotten your point across. Thanks for the condescending insight to your personality

5. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by FastCougar
Nevermind, this was extremely easy ... perhaps I should have read the BOL first!

Update tblName
Set fldName = round(fldName, 2)
Just as an FYI, Transact-SQL always rounds values ending in 5 away from zero. The statistically correct answer is to round the result to the even value (so some go up and some go down). This isn't a huge deal, unless you are doing statistically significant numbers of operations on values that end in 5. As an example:
Code:
```Original	-1.5	-0.5	0.5	1.5	2.5	3.5
T-SQL		-2.0	-1.0	1.0	2.0	3.0	4.0
Correct		-2.0	0.0	0.0	2.0	2.0	4.0```
-PatP

6. Registered User
Join Date
Feb 2004
Posts
42
Originally Posted by Pat Phelan
Just as an FYI, Transact-SQL always rounds values ending in 5 away from zero. The statistically correct answer is to round the result to the even value (so some go up and some go down). This isn't a huge deal, unless you are doing statistically significant numbers of operations on values that end in 5. As an example:
Code:
```Original	-1.5	-0.5	0.5	1.5	2.5	3.5
T-SQL		-2.0	-1.0	1.0	2.0	3.0	4.0
Correct		-2.0	0.0	0.0	2.0	2.0	4.0```
-PatP
I'm using SQL Server 2000, which requires that when using the Round() function that you use the length argument. Not sure about previous SQL implimentations.

Syntax
ROUND ( numeric_expression , length [ , function ] )

So, Round(1.5) will give an error because it requires 2 or 3 arguments. However, Round(1.5, 2) will give 1.5 whereas Round(1.5, 0) will return 2.0

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
This is probably making a mountain out of a mole-hill, so don't get too wired up in it unless the end result is significant to your database.

Using Transact-SQL, Round(2.5, 0) produces a result of 3. Mathematically (especially significant in statistics), it ought to produce a result of 2 because a value that ends exactly at 5 is supposed to round so that the result is an even digit (a 2 instead of a 3 in this case).

In the case of money (which is what I presume you are working with), any value with exactly a half cent should round to an even number of cents, never to an odd number of cents. VB has handled this correctly for some time (since at least VB 5.0), but Transact-SQL still rounds away from zero which is what the underlying C library does.

This isn't a big deal in most cases. It often makes no difference in the end result since debits and credits are often evenly enough distributed to make the net result balance after the rounding. It does require fiduciary disclosure if it is used for some kinds of financial data.

-PatP

8. Registered User
Join Date
Feb 2004
Posts
42
Originally Posted by Pat Phelan
This is probably making a mountain out of a mole-hill, so don't get too wired up in it unless the end result is significant to your database.

Using Transact-SQL, Round(2.5, 0) produces a result of 3. Mathematically (especially significant in statistics), it ought to produce a result of 2 because a value that ends exactly at 5 is supposed to round so that the result is an even digit (a 2 instead of a 3 in this case).

In the case of money (which is what I presume you are working with), any value with exactly a half cent should round to an even number of cents, never to an odd number of cents. VB has handled this correctly for some time (since at least VB 5.0), but Transact-SQL still rounds away from zero which is what the underlying C library does.

This isn't a big deal in most cases. It often makes no difference in the end result since debits and credits are often evenly enough distributed to make the net result balance after the rounding. It does require fiduciary disclosure if it is used for some kinds of financial data.

-PatP
Correct, I am using a money field and thus I need two decimal places and thus used the length argument of 2. Thanks for all the insight though ... much appreciated!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•