How to round decimals smaller than .5 to the following number in SQL?-CodePudding

I'm having this situation where a i have a large database with 1000 products.

Some of them have prices like 12.3, 20.7, 55.1 for example.

| Name     | Price          |
| -------- | -------------- |
| Product 1| 12.3           |
| Product 2| 20.7           |
| Product 3| 55.1           |

(and so on)...

What i've tried is update prices set price = ROUND (price, 0.1). The output for this will be:

| Name     | Price          |
| -------- | -------------- | (after updated)
| Product 1| 12.3           | 12.0
| Product 2| 20.7           | 21.0
| Product 3| 55.1           | 55.0

the prices with decimals < .5 will remain the same, and i'm out of ideas.

I'll appreciate any help.

Edit, i need to update all rows, i'm trying to learn about CEILING() but only shows how to use it with SELECT, any idea on how to perform an idk UPDATE CEILING or something?

CodePudding user response：

Use UPDATE prices SET PRICE = CEILING(PRICE); to round and set the values.

CodePudding user response：

It's not entirely clear what you're asking, but I can tell you the function call as shown makes no sense.

The second argument to the ROUND() function is the number of decimal places, not the size of the value you wish to round to. Additionally, the function only accepts integral types for that argument. Therefore, if you pass the value 0.1 to the function what will happen is the value is first cast to an integer, and the result of casting 0.1 to an integer is 0.

We see then, that calling ROUND(price, 0.1) is the same as calling ROUND(price, 0).

If you want to round to the nearest 0.1, that's one decimal place and the correct value for the ROUND() function is 1.

ROUND(price, 1)

Compare results here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7878c275f0f9ea86f07770e107bc1274

Note the trailing 0's remain, because the fundamental type of the value is unchanged. If you also want to remove the trailing 0`s, then you're really moving into the realm of strings, and for that you should wait and the client code, application, or reporting tool handle the conversion.