## Roundoff error in sql

round function in sql with negative parameter

sql ceiling to 2 decimal places

sql prevent rounding

sql server decimal auto rounding

sql float

sql not rounding correctly

sql server default rounding

DECLARE @TAX VARCHAR(30)=120.45 DECLARE @TaxRoundOf VARCHAR(30) SET @TaxRoundOf=ROUND(@TAX,1) SELECT @TaxRoundOf

*This Gives Result (@TaxRoundOf=120.5)*

DECLARE @TAX VARCHAR(30)=146.45 DECLARE @TaxRoundOf VARCHAR(30) SET @TaxRoundOf=ROUND(@TAX,1) SELECT @TaxRoundOf

*This Gives Result (@TaxRoundOf=146.4)*

But I need to return `146.50`

. why this mismatch between two results?
any one can help plz?

You can also rely on `numeric`

rounding instead of converting your string to a `float`

, which can lose information.

Cast a string to `numeric`

and then round:

select round(cast('146.45' as numeric(18,2)), 1) -- 146.50

A decimal constant is already a `decimal`

so there's no need to cast it:

select round(146.45, 1) -- 146.50

**Automatic decimal rounding issue,** tl;dr. Don't do calculations in SQL language. Longer. The result scale and precision is well defined here on MSDN. It isn't intuitive, really. Rounding errors is due to finite representation of real numbers in the computer. Computer systems can not work with real numbers accurate, but only with rational approximations thereof. Consequently, the actual numbers can not be represented in the computer than with a finite number of significant figures.

Since you are using `VARCHAR`

to store your numbers, SQL Server is having to do implicit conversion to float behind the scenes, which is having knock on effects on your calculations. You can reproduce this using the below query:

SELECT ROUND(CONVERT(FLOAT, 120.45),1), -- 120.5 ROUND(CONVERT(FLOAT, 146.45),1), -- 146.4 ROUND(CONVERT(DECIMAL(10, 2), 120.45),1), -- 120.50 ROUND(CONVERT(DECIMAL(10, 2), 146.45),1) -- 146.50

Since floating point numbers are not exact, 146.45 cannot be exactly represented as a float, and ends up being stored as a very slightly smaller number, so when this is passed to the round function, it is rounded down, instead of up.

The solution, as demonstrated by the 3rd and 4th columns in the above query, is to use a more precise data type.

**Overview of SQL Server Rounding Functions – SQL Round, Ceiling ,** We use 'SQL Server rounding function' like SQL Round, Ceiling and Floor to round the values to the nearest numbers. We perform an arithmetic Finally calculating the sum and rounding to two decimal and stores in a temporary table and SQL Server rounding Error, Giving different values In sql server

You can use this:

SET @TaxRoundOf=ROUND(10 * CAST(@TAX AS FLOAT)) / 10

instead of:

SET @TaxRoundOf=ROUND(@TAX,1)

PS as @GarethD already mentioned I wouldn't use `@TAX`

as `VARCHAR`

type.

**SQL Server ROUND() Function,** That's because SQL Server Management Studio (SSMS) rounds the You need to keep rounding in mind when you calculate decimal values. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length. Is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded.

**https://social.msdn.microsoft.com/Forums/SqlServer,** Find out how SQL Server calculates the result precision and scale for various operations. The number to be rounded. decimals. Required. The number of decimal places to round number to. operation. Optional. If 0, it rounds the result to the number of decimal. If another value than 0, it truncates the result to the number of decimals.

**SQL: Newbie Mistake #1: Using float instead of decimal,** A fair algorithm would distribute the rounding error 'randomly and evenly' instead. Besides this minor flaw, things start getting complicated if you A recommendation to avoid use of the MONEY or SMALLMONEY datatypes is included as a “Best Practice” code analysis rule in SQL Prompt (BP022). Rounding errors when using MONEY datatype The MONEY and SMALLMONEY data types are accurate to roughly a ten-thousandth of the monetary units that they represent.

**SQL Server's Rounding Secrets,** The default behavior for SQL Server is to round half away from zero as shown on Table 1. Original Value, Resultant value when rounding to truncation and roundoff errors • The truncation error generally increases as the step size increases, while the roundoff error decreases as the step size increases - this leads to a point of diminishing

##### Comments

- it's quite evident that the round function is rounding down. You must use the proper function to round up instead.
- Why are you using
`VARCHAR`

? If you were to store your numbers using a numeric data type, you would get the results you are expecting. - Maybe he wants to concatenate that value to some string.
- Convert to decimal or numeric and then apply round function. Should work
- Why would you be rounding values with two decimal places (xxx.45) to a value with two decimal places but rounded to only 1 (xxx.50). That seems unusual.
- Sir In this case its works but if @tax= 146.44 it return 146.50 but right answer is 146.40 is it sir?
- It depends on you which is 'right' answer. I have added in 'DEMO' ROUND and CEIL. Just pick up, what is better for you ;)