Why SQL Server cut precision to 3 or 6 places

user_5741056 0
Viewed 90

I have a big problem with calculations in sql(sql server management studio)

I have a variable declare @KursEur decimal(32,10) = 4.2617000000

I need 10 places after dot presicion

I need the result of multiplying the values ​​from the database with a variable with 10 places after the dot precision

ColumnName = decimal(32,10)

examples:

select ColumnName*@KursEUR 
from TableName

Result: 364947858427.467456

 select ColumnName*4.2617000000 
 from TableName

Result: 364947858427.46745556204873 <-i need this result

I tried it but it doesn't give the expected result :

 select CAST(ColumnName as numeric(32,10))* CAST(@KursEUR as numeric(32,10)) 
 from TableName

select CAST(ColumnName as float)* CAST(@KursEUR as float) 
 from TableName

 select CAST(ColumnName  as numeric(32,10))* CAST(@KursEUR as float) 
 from TableName

 select CAST(ColumnName as float)* CAST(@KursEUR as numeric(32,10)) 
 from TableName

 select ColumnName* CAST(@KursEUR as float) 
 from TableName;
select ColumnName* CAST(@KursEUR as numeric(32,10)) 
 from TableName

select CAST(ColumnName as numeric(32,10))* @KursEUR 
 from TableName

select CAST(ColumnName as float)* @KursEUR
 from TableName

 select CAST(ColumnName*@KursEUR as float) 
 from TableName

 select CAST(ColumnName*@KursEUR as numeric(32,10)) 
 from TableName
precision sql-server sql
1 Answers

When you multiply two numbers in SQL Server, the product's precision is determined by adding the precision from both numbers together plus one. In your case, if both numbers have a precision of 32, then the resulting product would need a precision of 65! The max is 38 (or smaller on earlier versions)!!

The trick is to use smaller variable sizes. If you cast @KursEur as decimal(11,10). and cast the ColumnName as something like decimal(25,10) it will work. Balance the precision between the two numbers as your needs require, just make sure they add up to 37 or less.

SELECT CAST(85634338040.5630277969 AS DECIMAL(25,10)) * CAST(4.2617000000 AS DECIMAL(11,10))
user_47360690
Related