We're seeing some rounding errors in a financial application. We had chosen
to use Decimal(38, 10) fields for both dollar amounts and allocation
percentages. However, when we multiply the numbers together we loose
significant precision, as in the following example:
DECLARE @.TNA_PERCENT AS DECIMAL(38, 10)
DECLARE @.ALLOC_PERCENT AS DECIMAL(38, 10)
DECLARE @.Result AS DECIMAL(38, 10)
SET @.TNA_PERCENT = .7363612218
SET @.ALLOC_PERCENT = .01
SET @.Result = @.TNA_PERCENT * @.ALLOC_PERCENT
SELECT @.TNA_PERCENT, @.ALLOC_PERCENT, @.Result
Returns:
.7363612218 .0100000000 .0073640000
Granted, the Decimal(38, 10) data type is overkill for the value .01, but it
was one example that came up. Why do we lose the precision during this
multiplication operation? Should we revise our data types, and if so, to
what? We will be dealing with large dollar amounts that need to be multiplie
d
accurately by calculated allocation percentages and stored.
Thanks,
Craig RandallCraig,
If you multiply a decimal 38 with another decimal 38, then the result
would be a decimal 76 (38+38). Since your result variable is restricted
to 38 digits, you loose precision.
If you want an exact result in a decimal(38,10), then the two
multiplication variables should have a combined total of at most 38.
Same for the precision. So if you multiply a decimal(19,5) with a
decimal(19,5), then this will fit in a decimal(38,10).
You will see that if you change the definition of @.TNA_PERCENT and
@.ALLOC_PERCENT to Decimal(19,10) that the result will be:
.7363612218 .0100000000 .0073636122
In this case, to get an exact result, you would need a precision of (at
least) 12 digits.
Gert-Jan
Crandaddy wrote:
> We're seeing some rounding errors in a financial application. We had chose
n
> to use Decimal(38, 10) fields for both dollar amounts and allocation
> percentages. However, when we multiply the numbers together we loose
> significant precision, as in the following example:
> DECLARE @.TNA_PERCENT AS DECIMAL(38, 10)
> DECLARE @.ALLOC_PERCENT AS DECIMAL(38, 10)
> DECLARE @.Result AS DECIMAL(38, 10)
> SET @.TNA_PERCENT = .7363612218
> SET @.ALLOC_PERCENT = .01
> SET @.Result = @.TNA_PERCENT * @.ALLOC_PERCENT
> SELECT @.TNA_PERCENT, @.ALLOC_PERCENT, @.Result
> Returns:
> .7363612218 .0100000000 .0073640000
> Granted, the Decimal(38, 10) data type is overkill for the value .01, but
it
> was one example that came up. Why do we lose the precision during this
> multiplication operation? Should we revise our data types, and if so, to
> what? We will be dealing with large dollar amounts that need to be multipl
ied
> accurately by calculated allocation percentages and stored.
> Thanks,
> Craig Randall
Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts
Wednesday, March 7, 2012
Rounding problems
I have rounding problems when editing or inserting a new record in float type fields.
e.g. I have a cursor running an agrregate SQL statement. I have a calculated field Sum(DFactor*Cost). DFactor gets values -1,1 and values of Cost in the table have 2 digits. I get these values in a variable e.g. @.FCost. Then I round @.FCost=Round(@.FCost,2).
When I try to inert this value to a new record again I'using Round(@.FCost,2).
However in a lot of records a lot of digits are stored.
I have the same probelm when trying to insert values from MSAccess by ODBC. Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.
What is the best practise in order to solve this problem?
Regards,
ManolisIf you are using a FLOAT column to store data of type MONEY, that's a problem. If you are using a FLOAT column to store data of type DECIMAL (x, 2), that's also a problem. Is your underlying problem one of datatype, not actually rounding?
-PatP|||Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.The result will have decimals. You need this: CLng(@.FCost*100/100)
e.g. I have a cursor running an agrregate SQL statement. I have a calculated field Sum(DFactor*Cost). DFactor gets values -1,1 and values of Cost in the table have 2 digits. I get these values in a variable e.g. @.FCost. Then I round @.FCost=Round(@.FCost,2).
When I try to inert this value to a new record again I'using Round(@.FCost,2).
However in a lot of records a lot of digits are stored.
I have the same probelm when trying to insert values from MSAccess by ODBC. Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.
What is the best practise in order to solve this problem?
Regards,
ManolisIf you are using a FLOAT column to store data of type MONEY, that's a problem. If you are using a FLOAT column to store data of type DECIMAL (x, 2), that's also a problem. Is your underlying problem one of datatype, not actually rounding?
-PatP|||Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.The result will have decimals. You need this: CLng(@.FCost*100/100)
Rounding issue in calculated field using round (,1) function
I have a couple of calculated fields in a BI Dev Studio Report as follows:
1. round(Fields!CM_Perf_1st_Mth.Value, 1)
2. round(Fields!CM_Perf_1st_Mth.Value, 1) - round(Fields!BM_Perf_1st_Mth.Value, 1)
The first calc field above is returning wrong results i.e. for a value of 2.25, instead of returning 2.3, it is returning 2.2. Similarly for -0.05, it is returning 0.0, instead of -0.1.
Since the results from the first function are wrong, the second function is also returning off values.
Has anyone faced this issue? How does one get around this? I have SQL 2005 Reporting Services with SP1. The result was the same without SP1 also. Seems like a big bug in the round function...
TIA.
Had similiar issues and went with doing rounding functions in a custom code function.
Try using the functions there, you will see a difference.
Daryl
|||The dataset is coming from a SQL Server 2000 database stored proc.|||doesn't matter where the dataset is coming from. use the =Code.myroundfunction in the field, and pass the field to the code function.
IE:
field1
if you had "=round(field!myfield.value)"
change it to =code.myroundfunction(field!myfield.value)
|||what does the raw sql data look like?|||Wrote a custom code fx as:
Public Function MyRound(byVal x as decimal, byval y as int16) as decimal
return round(x,y)
end function
Calling this from the calculated field expression does not make a difference at all.
And here's the raw data:
Composite_Code LU_Weight_Type_Code Perf_Date LU_Data_Stage Perf_Gross
Composite_Code LU_Weight_Type_Code Perf_Date LU_Data_Stage Perf_Gross
Any other ideas would be greatly appreciated as I am stuck on this and the deliverable is long due and this is the only issue left... TIA.
|||Here is a link to another forum which should help you out.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=315706&SiteId=1
Daryl
|||This function works for me.
Public Function MyRound(ByVal x As Decimal, ByVal y As Integer) As Decimal
Return Round(x, y, MidpointRounding.AwayFromZero)
End Function
the definition for round is really confusing.
Daryl
Saturday, February 25, 2012
Rounding a real number
Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
Harald
REAL is an inexact numeric type. If you want more precise results then you
should use a precise type such as NUMERIC. Either change the column or CAST
it. On the other hand if you just want to format the value to a certain
number of decimals that's probably easier to do in your client-side app.
David Portas
SQL Server MVP
|||Hello,
I understand, that REAL is not an exact numeric type.
But I think this should work (depth is REAL type):
Query Analyzer:
update dims set depth = 4.4
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
Harald
|||The ROUND() function returns a number in the data type of the input expression. If NUMERIC, returns NUMERIC; if FLOAT, returns FLOAT. A NUMERIC is an exact data type where FLOAT is inexact. Since your input was implicitly FLOAT, the function returned a FLOAT; however, notice that the results did ROUND to two significant digits. The trailing digits were due to the nature of FLOAT not ROUND. Had you CAST your results to NUMERIC, I think you would have gained what you were searching for. The question is whether or not you also needed to ROUND.
SELECT CAST(4 * 1.1 AS NUMERIC(2, 1))
This does no rounding but returns the result 4.4 exactly.
Sincerely,
Anthony Thomas
"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OCSkGp80EHA.3588@.TK2MSFTNGP14.phx.gbl...
Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
Harald
|||Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald
|||> it works in a SELECT statement, but not in an UPDATE statement.
If you put CAST(4.4 AS NUMERIC(9,2)) in a SELECT statement you are
outputting a NUMERIC column not a REAL. Again, REAL is NOT exact. You CANNOT
expect to control precisely the value of the digits to the right of the point
with a REAL. If this is a problem for you then you shouldn't be using REAL.
David Portas
SQL Server MVP
|||The problem is that the data type you are using is REAL (i.e., FLOAT); it is a FLOATING DECIMAL POINT data type. This means you store a mantissa and an exponent to a significant number of digits. This does not mean you don't carry around additional digits, just that for your precision and scale, you will have the correct result, but anything beyond these bounds and you will end up with extra stuff.
You will get the same behavior in your programming API if you store a value in a SINGLE FLOAT (4 bytes) variable and then covert that variable to a DOUBLE FLOAT (8 bytes).
This is the nature of using floating point arithmetic.
If you need the precision, change your DEPTH variable to a NUMERIC instead of FLOAT.
So you know, this problem has been around far longer than SQL Server, or any RDBMS'. This is the nature of the computer and finite mathematics.
Sincerely,
Anthony Thomas
"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OzR2E090EHA.3364@.TK2MSFTNGP12.phx.gbl...
Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald|||Harald,
The data type REAL cannot store the value 4.4. The closest REAL value to
4.4 is what you are seeing. You wouldn't expect an INTEGER column to hold
the value 1.3, and it's the same story here. REAL can only hold certain
numbers, and 4.4 is not one of them.
Steve Kass
Drew University
"Harald Witt" <hwitt@.novasoftware.de> wrote in message
news:OUlBBh90EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I understand, that REAL is not an exact numeric type.
> But I think this should work (depth is REAL type):
> Query Analyzer:
> update dims set depth = 4.4
> Select depth from dims
> Result: 4.4000001
> Any advise?
> Thanks in advance,
> Harald
>
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
Harald
REAL is an inexact numeric type. If you want more precise results then you
should use a precise type such as NUMERIC. Either change the column or CAST
it. On the other hand if you just want to format the value to a certain
number of decimals that's probably easier to do in your client-side app.
David Portas
SQL Server MVP
|||Hello,
I understand, that REAL is not an exact numeric type.
But I think this should work (depth is REAL type):
Query Analyzer:
update dims set depth = 4.4
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
Harald
|||The ROUND() function returns a number in the data type of the input expression. If NUMERIC, returns NUMERIC; if FLOAT, returns FLOAT. A NUMERIC is an exact data type where FLOAT is inexact. Since your input was implicitly FLOAT, the function returned a FLOAT; however, notice that the results did ROUND to two significant digits. The trailing digits were due to the nature of FLOAT not ROUND. Had you CAST your results to NUMERIC, I think you would have gained what you were searching for. The question is whether or not you also needed to ROUND.
SELECT CAST(4 * 1.1 AS NUMERIC(2, 1))
This does no rounding but returns the result 4.4 exactly.
Sincerely,
Anthony Thomas
"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OCSkGp80EHA.3588@.TK2MSFTNGP14.phx.gbl...
Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
Harald
|||Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald
|||> it works in a SELECT statement, but not in an UPDATE statement.
If you put CAST(4.4 AS NUMERIC(9,2)) in a SELECT statement you are
outputting a NUMERIC column not a REAL. Again, REAL is NOT exact. You CANNOT
expect to control precisely the value of the digits to the right of the point
with a REAL. If this is a problem for you then you shouldn't be using REAL.
David Portas
SQL Server MVP
|||The problem is that the data type you are using is REAL (i.e., FLOAT); it is a FLOATING DECIMAL POINT data type. This means you store a mantissa and an exponent to a significant number of digits. This does not mean you don't carry around additional digits, just that for your precision and scale, you will have the correct result, but anything beyond these bounds and you will end up with extra stuff.
You will get the same behavior in your programming API if you store a value in a SINGLE FLOAT (4 bytes) variable and then covert that variable to a DOUBLE FLOAT (8 bytes).
This is the nature of using floating point arithmetic.
If you need the precision, change your DEPTH variable to a NUMERIC instead of FLOAT.
So you know, this problem has been around far longer than SQL Server, or any RDBMS'. This is the nature of the computer and finite mathematics.
Sincerely,
Anthony Thomas
"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OzR2E090EHA.3364@.TK2MSFTNGP12.phx.gbl...
Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald|||Harald,
The data type REAL cannot store the value 4.4. The closest REAL value to
4.4 is what you are seeing. You wouldn't expect an INTEGER column to hold
the value 1.3, and it's the same story here. REAL can only hold certain
numbers, and 4.4 is not one of them.
Steve Kass
Drew University
"Harald Witt" <hwitt@.novasoftware.de> wrote in message
news:OUlBBh90EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I understand, that REAL is not an exact numeric type.
> But I think this should work (depth is REAL type):
> Query Analyzer:
> update dims set depth = 4.4
> Select depth from dims
> Result: 4.4000001
> Any advise?
> Thanks in advance,
> Harald
>
Rounding a real number
Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
HaraldREAL is an inexact numeric type. If you want more precise results then you
should use a precise type such as NUMERIC. Either change the column or CAST
it. On the other hand if you just want to format the value to a certain
number of decimals that's probably easier to do in your client-side app.
--
David Portas
SQL Server MVP
--|||Hello,
I understand, that REAL is not an exact numeric type.
But I think this should work (depth is REAL type):
Query Analyzer:
update dims set depth = 4.4
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
Harald|||This is a multi-part message in MIME format.
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=3DCAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello,
it works in a SELECT statement, but not =in an UPDATE statement.
Update dims Set depth=3DCAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0--|||> it works in a SELECT statement, but not in an UPDATE statement.
If you put CAST(4.4 AS NUMERIC(9,2)) in a SELECT statement you are
outputting a NUMERIC column not a REAL. Again, REAL is NOT exact. You CANNOT
expect to control precisely the value of the digits to the right of the point
with a REAL. If this is a problem for you then you shouldn't be using REAL.
--
David Portas
SQL Server MVP
--|||Harald,
The data type REAL cannot store the value 4.4. The closest REAL value to
4.4 is what you are seeing. You wouldn't expect an INTEGER column to hold
the value 1.3, and it's the same story here. REAL can only hold certain
numbers, and 4.4 is not one of them.
Steve Kass
Drew University
"Harald Witt" <hwitt@.novasoftware.de> wrote in message
news:OUlBBh90EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I understand, that REAL is not an exact numeric type.
> But I think this should work (depth is REAL type):
> Query Analyzer:
> update dims set depth = 4.4
> Select depth from dims
> Result: 4.4000001
> Any advise?
> Thanks in advance,
> Harald
>
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
HaraldREAL is an inexact numeric type. If you want more precise results then you
should use a precise type such as NUMERIC. Either change the column or CAST
it. On the other hand if you just want to format the value to a certain
number of decimals that's probably easier to do in your client-side app.
--
David Portas
SQL Server MVP
--|||Hello,
I understand, that REAL is not an exact numeric type.
But I think this should work (depth is REAL type):
Query Analyzer:
update dims set depth = 4.4
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
Harald|||This is a multi-part message in MIME format.
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=3DCAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello,
it works in a SELECT statement, but not =in an UPDATE statement.
Update dims Set depth=3DCAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0--|||> it works in a SELECT statement, but not in an UPDATE statement.
If you put CAST(4.4 AS NUMERIC(9,2)) in a SELECT statement you are
outputting a NUMERIC column not a REAL. Again, REAL is NOT exact. You CANNOT
expect to control precisely the value of the digits to the right of the point
with a REAL. If this is a problem for you then you shouldn't be using REAL.
--
David Portas
SQL Server MVP
--|||Harald,
The data type REAL cannot store the value 4.4. The closest REAL value to
4.4 is what you are seeing. You wouldn't expect an INTEGER column to hold
the value 1.3, and it's the same story here. REAL can only hold certain
numbers, and 4.4 is not one of them.
Steve Kass
Drew University
"Harald Witt" <hwitt@.novasoftware.de> wrote in message
news:OUlBBh90EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I understand, that REAL is not an exact numeric type.
> But I think this should work (depth is REAL type):
> Query Analyzer:
> update dims set depth = 4.4
> Select depth from dims
> Result: 4.4000001
> Any advise?
> Thanks in advance,
> Harald
>
Rounding a real number
Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
HaraldREAL is an inexact numeric type. If you want more precise results then you
should use a precise type such as NUMERIC. Either change the column or CAST
it. On the other hand if you just want to format the value to a certain
number of decimals that's probably easier to do in your client-side app.
David Portas
SQL Server MVP
--|||Hello,
I understand, that REAL is not an exact numeric type.
But I think this should work (depth is REAL type):
Query Analyzer:
update dims set depth = 4.4
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
Harald|||The ROUND() function returns a number in the data type of the input expressi
on. If NUMERIC, returns NUMERIC; if FLOAT, returns FLOAT. A NUMERIC is an
exact data type where FLOAT is inexact. Since your input was implicitly FLO
AT, the function returned a FLOAT; however, notice that the results did ROUN
D to two significant digits. The trailing digits were due to the nature of
FLOAT not ROUND. Had you CAST your results to NUMERIC, I think you would ha
ve gained what you were searching for. The question is whether or not you a
lso needed to ROUND.
SELECT CAST(4 * 1.1 AS NUMERIC(2, 1))
This does no rounding but returns the result 4.4 exactly.
Sincerely,
Anthony Thomas
--
"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OCSkGp80EHA.35
88@.TK2MSFTNGP14.phx.gbl...
Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
Harald|||Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald|||> it works in a SELECT statement, but not in an UPDATE statement.
If you put CAST(4.4 AS NUMERIC(9,2)) in a SELECT statement you are
outputting a NUMERIC column not a REAL. Again, REAL is NOT exact. You CANNOT
expect to control precisely the value of the digits to the right of the poin
t
with a REAL. If this is a problem for you then you shouldn't be using REAL.
David Portas
SQL Server MVP
--|||The problem is that the data type you are using is REAL (i.e., FLOAT); it is
a FLOATING DECIMAL POINT data type. This means you store a mantissa and an
exponent to a significant number of digits. This does not mean you don't c
arry around additional digits, just that for your precision and scale, you w
ill have the correct result, but anything beyond these bounds and you will e
nd up with extra stuff.
You will get the same behavior in your programming API if you store a value
in a SINGLE FLOAT (4 bytes) variable and then covert that variable to a DOUB
LE FLOAT (8 bytes).
This is the nature of using floating point arithmetic.
If you need the precision, change your DEPTH variable to a NUMERIC instead o
f FLOAT.
So you know, this problem has been around far longer than SQL Server, or any
RDBMS'. This is the nature of the computer and finite mathematics.
Sincerely,
Anthony Thomas
--
"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OzR2E090EHA.33
64@.TK2MSFTNGP12.phx.gbl...
Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald|||Harald,
The data type REAL cannot store the value 4.4. The closest REAL value to
4.4 is what you are seeing. You wouldn't expect an INTEGER column to hold
the value 1.3, and it's the same story here. REAL can only hold certain
numbers, and 4.4 is not one of them.
Steve Kass
Drew University
"Harald Witt" <hwitt@.novasoftware.de> wrote in message
news:OUlBBh90EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I understand, that REAL is not an exact numeric type.
> But I think this should work (depth is REAL type):
> Query Analyzer:
> update dims set depth = 4.4
> Select depth from dims
> Result: 4.4000001
> Any advise?
> Thanks in advance,
> Harald
>
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
HaraldREAL is an inexact numeric type. If you want more precise results then you
should use a precise type such as NUMERIC. Either change the column or CAST
it. On the other hand if you just want to format the value to a certain
number of decimals that's probably easier to do in your client-side app.
David Portas
SQL Server MVP
--|||Hello,
I understand, that REAL is not an exact numeric type.
But I think this should work (depth is REAL type):
Query Analyzer:
update dims set depth = 4.4
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
Harald|||The ROUND() function returns a number in the data type of the input expressi
on. If NUMERIC, returns NUMERIC; if FLOAT, returns FLOAT. A NUMERIC is an
exact data type where FLOAT is inexact. Since your input was implicitly FLO
AT, the function returned a FLOAT; however, notice that the results did ROUN
D to two significant digits. The trailing digits were due to the nature of
FLOAT not ROUND. Had you CAST your results to NUMERIC, I think you would ha
ve gained what you were searching for. The question is whether or not you a
lso needed to ROUND.
SELECT CAST(4 * 1.1 AS NUMERIC(2, 1))
This does no rounding but returns the result 4.4 exactly.
Sincerely,
Anthony Thomas
--
"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OCSkGp80EHA.35
88@.TK2MSFTNGP14.phx.gbl...
Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
Harald|||Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald|||> it works in a SELECT statement, but not in an UPDATE statement.
If you put CAST(4.4 AS NUMERIC(9,2)) in a SELECT statement you are
outputting a NUMERIC column not a REAL. Again, REAL is NOT exact. You CANNOT
expect to control precisely the value of the digits to the right of the poin
t
with a REAL. If this is a problem for you then you shouldn't be using REAL.
David Portas
SQL Server MVP
--|||The problem is that the data type you are using is REAL (i.e., FLOAT); it is
a FLOATING DECIMAL POINT data type. This means you store a mantissa and an
exponent to a significant number of digits. This does not mean you don't c
arry around additional digits, just that for your precision and scale, you w
ill have the correct result, but anything beyond these bounds and you will e
nd up with extra stuff.
You will get the same behavior in your programming API if you store a value
in a SINGLE FLOAT (4 bytes) variable and then covert that variable to a DOUB
LE FLOAT (8 bytes).
This is the nature of using floating point arithmetic.
If you need the precision, change your DEPTH variable to a NUMERIC instead o
f FLOAT.
So you know, this problem has been around far longer than SQL Server, or any
RDBMS'. This is the nature of the computer and finite mathematics.
Sincerely,
Anthony Thomas
--
"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OzR2E090EHA.33
64@.TK2MSFTNGP12.phx.gbl...
Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald|||Harald,
The data type REAL cannot store the value 4.4. The closest REAL value to
4.4 is what you are seeing. You wouldn't expect an INTEGER column to hold
the value 1.3, and it's the same story here. REAL can only hold certain
numbers, and 4.4 is not one of them.
Steve Kass
Drew University
"Harald Witt" <hwitt@.novasoftware.de> wrote in message
news:OUlBBh90EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I understand, that REAL is not an exact numeric type.
> But I think this should work (depth is REAL type):
> Query Analyzer:
> update dims set depth = 4.4
> Select depth from dims
> Result: 4.4000001
> Any advise?
> Thanks in advance,
> Harald
>
Tuesday, February 21, 2012
Rotate fields
Hi folks,
Is there a way to rotate a field 90 degrees?
Thank's in advance,
Staffanyes check out the properties of the field there ll b a property like
DIRECTION (exactly name is not remebered) but that property helps
adjusting direction of the text in the textbox like left to right,right
to left, top to bottom etc check it ou|||Is there any documentation on the syntax for this?
I only see two options:
lr-tb
tb-lr
It seems that I am rotating the text Ok, but I just need it to be turned
180% degree the other way. Are there any docs on this? I've scoured the
forum but can't find any topics already on this.
Thank!
"** Spirits **" wrote:
> yes check out the properties of the field there ll b a property like
> DIRECTION (exactly name is not remebered) but that property helps
> adjusting direction of the text in the textbox like left to right,right
> to left, top to bottom etc check it ou
>|||as per my knowledge only these two possibilities lr-tb n tb-lr r
possible if u get anything else plz let me know as well
Is there a way to rotate a field 90 degrees?
Thank's in advance,
Staffanyes check out the properties of the field there ll b a property like
DIRECTION (exactly name is not remebered) but that property helps
adjusting direction of the text in the textbox like left to right,right
to left, top to bottom etc check it ou|||Is there any documentation on the syntax for this?
I only see two options:
lr-tb
tb-lr
It seems that I am rotating the text Ok, but I just need it to be turned
180% degree the other way. Are there any docs on this? I've scoured the
forum but can't find any topics already on this.
Thank!
"** Spirits **" wrote:
> yes check out the properties of the field there ll b a property like
> DIRECTION (exactly name is not remebered) but that property helps
> adjusting direction of the text in the textbox like left to right,right
> to left, top to bottom etc check it ou
>|||as per my knowledge only these two possibilities lr-tb n tb-lr r
possible if u get anything else plz let me know as well
Subscribe to:
Posts (Atom)