Results 1 to 8 of 8
  1. #1
    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

  2. #2
    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

  3. #3
    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. #4
    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

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,922
    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

  6. #6
    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

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,922
    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. #8
    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!

Posting Permissions

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