How to round 4 decimal places in SQL

Question

I am using MS SQL server. I have varchar columns that I need to convert to NUMERIC(19,4). I am trying to cast and round the field, however in some instances it seems to truncate down to 4 decimal places, rather than properly round. Excample: 17.654593 needs to be 17.6546 rather than just dropping off the '93' from the end. I am using the below to update the entire field. Is there a way to add a 'round' to this script so it updates properly?

update table_name
SET price = case when isnull(price,'') <> ''
     then CAST(CAST(price as FLOAT) as NUMERIC(19,4))
     else 0
     end

Answer 1

Problems

1. I would advise not casting to FLOAT. It is an inherently inexact datatype, and is probably the cause of the rounding problem. You should be able to cast directly to NUMERIC(19,4).
2. As this is a VARCHAR column, there's the possibility that a row might be holding a value which will cause your statement to fail. Try putting some text in that field and then running your fix - unless you're handling the error somewhere else, it will fail.
3. If you push this value back into the same VARCHAR column, it's just going to be implicitly converted back into a VARCHAR anyway. It will have been rounded to four decimal places, but it will still be VARCHAR. I'll presume that's really want you want to do, but have a think about whether it's the right solution.
4. Also, is there a good reason why you're replacing NULL with 0? Just a word of caution there, because the number zero and a NULL have very different meanings, and if you're not being careful this may trip up you or someone else who needs to consume the data at a later date.

---

Solutions

SQL Server 2012 or later, resolves rounding issue, resolves unsafe cast to NUMERIC, and leaves NULL values as NULL:

update table_name
set price = ISNULL(TRY_CAST(price as NUMERIC(19,4)), price);

SQL Server 2012 or later, resolves rounding issue, resolves unsafe cast to NUMERIC, sets NULL values to 0:

update table_name
set price = ISNULL(ISNULL(TRY_CAST(price as NUMERIC(19,4)), price), 0);

SQL Server 2008 or later, resolves rounding issue, resolves unsafe cast to NUMERIC, sets NULL values to 0:

update table_name
set price = case
              when price is NULL
                then '0'
              when ISNUMERIC(price + 'e0') = 1
                then CAST(CAST(price as NUMERIC(19,4)) as VARCHAR)
              else price
            end

If you want to stop NULLs from being converted into 0, simply remove the first when clause.