How do I get an accurate result (not a rounded result) in SQL Server 2008 R2?
I have searched on google and this stackoverflow before asking this question and I have not found any results that could solve my problem. Now I am running out of keywords to search for more. I wish someone here could help me.
Here is my problem: I am dealing with a lot of data and using SQL Server 2008 R2 as my database. I got some errors in data values and the reason for this is the same request:
SELECT (SELECT 93834718293817283242347.982763509735278) * (SELECT 100/100)
The result is 93834718293817283242347.982764
also causing the wrong value for my calculation. I want the result to be 93834718293817283242347.982763509735278
, how do I do it?
I tried another way like this and still fell short of expectations
SELECT CONVERT(DECIMAL(38,15), 93834718293817283242347.982763509735278) * CONVERT(DECIMAL(38,15), (100/100))
Exit 93834718293817283242347.982764
, but I want93834718293817283242347.982763509735278
And if you don't mind, I need an explanation. Thanks you
source to share
I don't really know how to solve this problem, but I am giving you some explanation of what is going on. Well, let's take a look at the properties of the values:
DECLARE @number SQL_VARIANT
SELECT @number =93834718293817283242347.982763509735278
SELECT @number
SELECT
SQL_VARIANT_PROPERTY(@number, 'BaseType') BaseType,
SQL_VARIANT_PROPERTY(@number, 'MaxLength') MaxLength,
SQL_VARIANT_PROPERTY(@number, 'Scale') Scale,
SQL_VARIANT_PROPERTY(@number, 'Precision') Precision
Second:
DECLARE @number SQL_VARIANT
SELECT @number =100.0/100.0
SELECT @number
SELECT
SQL_VARIANT_PROPERTY(@number, 'BaseType') BaseType,
SQL_VARIANT_PROPERTY(@number, 'MaxLength') MaxLength,
SQL_VARIANT_PROPERTY(@number, 'Scale') Scale,
SQL_VARIANT_PROPERTY(@number, 'Precision') Precision
And multiplication
DECLARE @number SQL_VARIANT
SELECT @number =CONVERT(DECIMAL(38,15), 93834718293817283242347.982763509735278) * (100/100)
SELECT @number
SELECT
SQL_VARIANT_PROPERTY(@number, 'BaseType') BaseType,
SQL_VARIANT_PROPERTY(@number, 'MaxLength') MaxLength,
SQL_VARIANT_PROPERTY(@number, 'Scale') Scale,
SQL_VARIANT_PROPERTY(@number, 'Precision') Precision
Take a close look at the result:
BaseType MaxLength Scale Precision
-------------------- -------------------- -------------------- -------------
decimal 17 6 38
As you know, you cannot declare 16 in Scale here CONVERT(DECIMAL(38,15), 93834718293817283242347.982763509735278)
, otherwise you will get Arithmetic overflow error converting numeric to data type numeric.
.
So, due to the folmulas here, SQL Server cannot add two scale values and beacause of the fact that it uses the default scale of 6 . See the following example, scale 5 :
SELECT CONVERT(DECIMAL(38,4), 93834718293817283242347.982763509735278) * CONVERT(DECIMAL(38,1), (100/100))
source to share