SQL - Round a dollar value and update it in SQL

Posted on 2005-04-14
Last Modified: 2008-03-06

I have a field in SQL Server 2000 titled "TotalAmount" and this is Money(8). I need to change the decimal places to 2 instead of 4.

For example, I have a value of 9.8895 in this field.  I need to write an UPDATE statement that will round this value to 9.89.

How can I do that?

Thanks in advance.
Question by:rudyflyer
    LVL 25

    Assisted Solution

    update yourtable set TotalAmount = round(TotalAmount,2)

    Author Comment

    If I do that I get trailing zeros on the end.

    For example, the value will be 45.0000  or 49.9500.

    I need it to be 45.00 or 49.95
    LVL 7

    Expert Comment

    The Money datatype has 4 decimal places, so you will always see the two trailing zeros if you keep that datatype.  If you want the column to only have 2 decimal places, you need to change the datatype to something like DECIMAL(16,2).  Before doing that, you need to execute an update statement to round all the values to two decimal places as suggested above.
    LVL 11

    Expert Comment

    Changing the datatype and saving will automatically round the values, but no reason to take a chance, run an UPDATE statement first.

    I'm not sure if you are looking to store data this way or just return data in this format. If you are looking to return data in this format, then you could use a convert() on select:
      SELECT CONVERT(decimal(16,2),TotalAmount) FROM table1

    If updating:
      UPDATE table1
      SET TotalAmount = ROUND(TotalAmount,2)
    LVL 25

    Expert Comment

    When you read the data, you can also use cast

    select Cast( round(TotalAmount,2) as decimal(16,2) ) as "TotalAmount"
    from yourtable

    The update statement is still correct, though.

    After doing the update, you can:

    select Cast(TotalAmount as decimal(16,2) ) as "TotalAmount"
    from yourtable
    LVL 2

    Expert Comment

    in mysql there exist a truncate() function

    mysql> SELECT TRUNCATE(-1.999,1);
            -> -1.9

    i dnt know if such function exist in MS SQL
    LVL 44

    Accepted Solution

    there is a VERY IMPORTANT reason why the Money datatype in your table has 4 digits after the decimal point, and if you are truly holding money values in these fields, then you should NOT change the field data type.

    The extra decimal digits are carried to properly handle round off when money fields are used in calculations, and when Money values are 'accumulated' (as in a Select SUM(MoneyField) as "Total...).  Without proper 'accounting' for the round off, over time, your calculaons will accumulate significant errors, and ultimately your money values will be wrong.

    If you are concerned about DISPLAYING values on forms or reports, then you can handle the rounding when the values are displayed, but you should not change how the values are actually stored in the database.  This woudl be a invitation to possible 'disaster'.

    LVL 11

    Expert Comment

    Arthur is correct. Not storing 4 digits to right of decimal to lead to greater problems.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now