Skip to content
Advertisement

# Is It Possible to Round a Number Smaller Than 1 In SQL While Keeping In Decimal Form

I am not finding the solution to this issue despite looking specifically for it.

The below query is me trying to get the last decimal places to appear properly.

```select CAST(CAST(33 as float)/100 as decimal(5,3))
```

From here I am looking for how to round properly to .33 or if the result were .339 to round up to .34

What I am finding is that it will only round down to 0 because it is not a whole number.

Can someone point me in the right direction or if there is no good way let me know.

## Answer

Make sure your denominator isn’t treated as an integer. You could tack on a `.0` or `CAST` or `CONVERT` it. In the case where you’d expect rounding down to occur:

```SELECT 331 / 1000.0;
-- gives 0.331000

SELECT ROUND(331 / 1000.0, 2);
-- gives 0.330000

SELECT CAST(ROUND(331 / 1000.0, 2) AS DECIMAL(5, 2));
-- gives 0.33
```

In the case where you’d be expect rounding up to occur:

```SELECT 339 / 1000.0;
-- gives 0.339000

SELECT ROUND(339 / 1000.0, 2);
-- gives 0.340000

SELECT CAST(ROUND(339 / 1000.0, 2) AS DECIMAL(5, 2));
-- gives 0.34
```

Note: you may not need to do both `ROUND` and `CAST` since I believe the rounding occurs implicitly, but it makes the intent more clear if you choose to write it as such.

2 People found this is helpful
Advertisement