Roundoff error in sql

how to avoid round off in sql server
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)

DEMO

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 ;)