Post reply

How to Trim Values if more than 2 numbers after '.'?

  • July 23, 2015 at 10:39 am

    #306017

    I am importing an excel spreadsheet into a MS SQL database table. When the spreadsheet is finished importing, I am noticing that some values that were brought in resemble something like this 1.41666666666667. Other values may be shorter or only have 1 digit. The problem is another web application that pulls this data for use in online forms only allows up to 2 digits. How can I round all of the numbers like the above to 2 decimals and replace the existing values?

    I know there is the rounding function that could be used like so:

    SELECT ROUND ([Hrs Total 2],2)

    FROM AnnualClassifiedPAFs

    How do I then take that rounded value and insert it back into the records?

  • July 23, 2015 at 11:10 am

    #1814467

    Technically, you don't take it and insert it back, you just update it. It might be the same thing worded differently, but it gives you the syntax needed.

    UPDATE AnnualClassifiedPAFs

    SET ROUND ([Hrs Total 2],2)

    WHERE [Hrs Total 2] <> ROUND ([Hrs Total 2],2) --Only update rows which need to be rounded.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 23, 2015 at 11:34 am

    #1814478

    Thank you for the feedback. I had to alter what you posted slightly to get it to work. See below:

    UPDATE AnnualClassifiedPAFs

    SET [Hrs Total 1] = (ROUND ([Hrs Total 1],2))

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply