CODEWITHSUNDEEP
sqlrounding
user3070963
user3070963

Reputation:

SQL Rounding down if x.xx5

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?

Upvotes: 3

Views: 680

Answers (3)

Chris Lawton
Chris Lawton

Reputation: 81

    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.

Upvotes: 0

georstef
georstef

Reputation: 1388

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

Upvotes: 1

ttaaoossuu
ttaaoossuu

Reputation: 7894

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

Upvotes: 3

Related Questions