If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Help withrounding money to 2 decimal places?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 42
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
Reply With Quote
  #2 (permalink)  
Old
The SQL Apostle
 
Join Date: Jul 2003
Location: The Dark Planet
Posts: 1,397
How about using this function ... http://thedailywtf.com/archive/2004/10/25/2882.aspx
__________________
Get yourself a copy of the The Holy Book

order has no physical Brett in The meaning of a Kaiser . -database data
Reply With Quote
  #3 (permalink)  
Old
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)
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 42
Quote:
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
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,735
Quote:
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 42
Quote:
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
Reply With Quote
  #7 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,735
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 42
Quote:
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!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On