Way of rounding of currency in SQL Server

Question

Given the following values (float data type), I want to ROUND them in the following way:

+----------------+--------+
| Original Value | Result |
+----------------+--------+
| 53.36          | 53.40  |
| 53.34          | 53.30  |
| 53.35          | 53.35  |  --Do not round up when 5
+----------------+--------+

Is there is a way to do this using T-SQL?

`FLOAT` is notoriously susceptible to rounding errors - if you're working with currency and need precise values, I would strongly recommend using `DECIMAL(p,s)` instead ! — marc_s, Mar 14 '18 at 05:47
Why are you using float for currency? Do you really need a range -1.79E+308 to -2.23E-308? TSQL has a money data type. — paparazzo, Mar 14 '18 at 08:22
The existing system is using that. I don't know why and I'm new to this system. @paparazzo :) — Hasics, Mar 14 '18 at 08:27

Brien Foss · Accepted Answer · 2018-03-14T04:31:44.660

Since you need your value to remain xx.x5 when found but ROUND otherwise, the following will work for you:

(IIF available in SQL Server 2012+)

DECLARE @val FLOAT = 53.35;
SELECT IIF ((RIGHT(@val,1) = 5), @val, ROUND(@val,1)) result

SET @val = 53.34
SELECT IIF ((RIGHT(@val,1) = 5), @val, ROUND(@val,1)) result

SET @val = 53.36
SELECT IIF ((RIGHT(@val,1) = 5), @val, ROUND(@val,1)) result

Here is a good resource to read about differences of types, specifically read about float types. Seems that's possibly not a good datatype for the values you have. Just something to consider.

Way of rounding of currency in SQL Server

1 Answers1