I have a SQL query
select
ExchangeRate,
TotalAmt,
ExchangeRate * TotalAmt TotalAmtConvert,
ROUND(ExchangeRate * TotalARAmt, 0) TotalAmtRound
from
dbo.Table
My results:
I want TotalAmtRound = 372527892
Can you help me?
This is the reason use should not use FLOAT. It is a approximate datatype. Always use DECIMAL or NUMERIC datatypr
DECLARE @ExchangeRate NUMERIC(22, 6) = 22450,
@TotalAmt NUMERIC(22, 6) = 16593.67,
@ExchangeRate1 FLOAT = 22450,
@TotalAmt1 FLOAT = 16593.67
SELECT numeric_result = Round(@ExchangeRate * @TotalAmt, 0), --Correct
float_result = Round(@ExchangeRate1 * @TotalAmt1, 0), --Incorrect
converted_numeric_result= Round(Cast(@ExchangeRate1 AS NUMERIC(22, 6))
* Cast(@TotalAmt1 AS NUMERIC(22, 6)), 0) --Correct
when you select @ExchangeRate1 * @TotalAmt1 it gives 372527891.49999994 but @ExchangeRate * @TotalAmt gives 372527891.5.
Result :
╔════════════════╦═══════════════╦══════════════════════════╗
║ numeric_result ║ float_result ║ converted_numeric_result ║
╠════════════════╬═══════════════╬══════════════════════════╣
║ 372527892 ║ 372527891 ║ 372527892 ║
╚════════════════╩═══════════════╩══════════════════════════╝
ROUND and CEILING functions are different. example ROUND(12.2,0) will give 12 and CEILING(12.2) will give 13Use CEILING() instead of ROUND()
CEILING - Evaluates the value on the right side of the decimal and returns the smallest integer greater than, or equal to, the specified numeric expression and accepts one value:
https://www.mssqltips.com/sqlservertip/1589/sql-server-rounding-functions--round-ceiling-and-floor/