SQL Rounding down if x.xx5

Question

Is there a way to round down instead of up where values have .005?

E.g.

1.234 -> 1.23

1.235 -> 1.23

1.236 -> 1.24

It's for invoicing purposes. I currently have an invoice which is showing totals of: £46.88 + £9.38 = £56.25 (the Grand total is what was agreed, but clearly the net & vat are misleading and don't add up exactly)

I want to round down the net to £46.87

EDIT: What about a possible alternative to stop rounding altogether and just display to 2 decimal places? My Grand Total is calculating correctly anyway as it pulls from source rather than adding rounded subtotals. If I prevent the subtotals from rounding at all then they should display e.g. 1.23 and cut off .xx5xxx?

then 0.005 + 0.005 = 0.00 instead of 0.01. Your best shot is to explain to show the max possible precision or to sum the values already truncated — mucio, Jun 11 '14 at 10:10
Sounds like you want [Banker's rounding](http://www.sqlservercentral.com/Forums/Topic246556-8-1.aspx) — Hans Keﬆing, Jun 11 '14 at 10:37
@mucio I'm only wanting to apply it to net, not vat. So it would be 0 + 0.005 (rounded to 0.01) = 0.01... instead of the incorrect 0.02 as it would appear currently. juergen d Exactly... though I'm sure they wouldn't argue over 0.01... 56.25 is what was agreed. 56.26 would be incorrect. — , Jun 11 '14 at 10:47

score 3 · Accepted Answer · answered Jun 11 '14 at 10:30

3

Assuming you're talking SQL Server:

DECLARE @val AS money
SET @val = 5.555
--SET @val = 5.556

SELECT
    CASE
        WHEN (CAST(FLOOR(@val*1000) as int) % 10) <= 5
        THEN FLOOR(@val*100) / 100
        ELSE CEILING(@val*100) / 100
    END

answered Jun 11 '14 at 10:30

ttaaoossuuuu

7,786
3
28
58

georstef · Answer 2 · 2014-06-11T11:25:13.273

For MySQL use select TRUNCATE(44.875, 2);

For SQLServer use select ROUND(44.875, 2, 1)

A good trick is to multiply by 100 take the integer part and divide that with 100

(in SQLServer select FLOOR(44.875 * 100)/100)

Update:

As I read better the question I saw that x.xx5 should round down and x.xx6 should round up so I add this to the answer (example in SQLServer but should not be much different in other DBs) :

select 
  CASE WHEN FLOOR(44.875 * 1000)-FLOOR(44.875 * 100)*10 <= 5 THEN
    FLOOR(44.875 * 100)/100
  ELSE
    ROUND(44.875, 2)
  END

score 0 · Answer 3 · answered Jan 05 '16 at 15:51

    select ROUND ( @val+0.004 , 2 , 1 )

the extra 1 at the end truncates in mssql rather than rounds, adding 0.004 will boost anything at or over .006 to the next number up, anything below will be rounded down. Similar could be used on other sql's to covert to numeric 10,2 or similar to obtain this truncation, just the adding 0.004 does what you need.

SQL Rounding down if x.xx5

3 Answers3