upto 2 decimals

Question

Post reply

upto 2 decimals

saama5

SSC Enthusiast

Points: 118
More actions
March 19, 2003 at 1:34 pm

#81286

I am inserting a value in float like 20.89 and it is inserting like 20.899999999999999
I just want it to store exact value like 20.89.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 1

What is your exact table CREATE statement and exact INSERT statement?

saama5 SSC Enthusiast Points: 118 More actions · Answer 2

Insert is thru stored procedure

create procedure @test1

@val1 float,

@val2 int

as

insert into testtable

(TestField1, testfield2)

value

(@val1, @val2)

saama5 SSC Enthusiast Points: 118 More actions · Answer 3

saama5

SSC Enthusiast

Points: 118

March 19, 2003 at 2:31 pm

#451485

sorry procedure name is without @

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 4

Then you should not use float, use something like decimal(9,2) instead.

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 5

Is TestField1 column of type float? What if you output @val1 rught before the INSERT statement? What does it print?

You forgot to show me the CREATE TABLE statement.

I actualy would not be surprised to see 20.8899999999 but 20.89999999 does not make sense.

saama5 SSC Enthusiast Points: 118 More actions · Answer 6

create procedure test1

@val1 float,

@val2 int

as

declare @val3 decimal(9,2)

select @val3 = @val1

insert into testtable1

(TestField1, testfield2)

values

(@val3, @val2)

-----------------------

exec test1 20.8,12

I did it and it is still storing it

20.800000000000001

saama5 SSC Enthusiast Points: 118 More actions · Answer 7

Before insert also it is like this

20.800000000000001.

I don't want to change it to decimal. Is there no way to fix it to 2 decimals only

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 8

Why do you not want to change it? The float data type is for storing approximations and uses scientific notation to store the values. You will never be able to store it with two decimals. If you want to you can convert it to decimal when retrieving it if you're only after showing it in with two decimals.

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

saama5 SSC Enthusiast Points: 118 More actions · Answer 9

Sometimes we start thinking impossible.

Anyway Thanks!!

lambje SSCrazy Points: 2085 More actions · Answer 10

Strange. This works for me:

create table t_test ( val1 float not null )

go

create procedure p_test

@val float

as

begin

insert into t_test values (@val)

end

go

p_test 20.8

go

select * from t_test

go

/*

val1

20.8

*/

shendricks SSChasing Mays Points: 635 More actions · Answer 11

quote:
create procedure test1
@val1 float,
@val2 int
as
declare @val3 decimal(9,2)
select @val3 = @val1
insert into testtable1
(TestField1, testfield2)
values
(@val3, @val2)
-----------------------
exec test1 20.8,12
I did it and it is still storing it
20.800000000000001

If your data column is still defined to be of type float you are going to get appreciable round-off error. Chris Hedgate was quite accurate is stating that float and real (ironically) don't store the real value but rather an approximation. Converting your float parameter (@Val1) to decimal(9, 2) but then storing it in a float data column cannot preserve accuracy.

If you want accuracy, you cannot use an approximation.

Remember, also, that the database is responsible for the data proper. How that data gets formatted on the page or report is a function of the application.

HTH

Steve Hendricks

MCSD, MCDBA

AFS Consulting Group

shendricks@afsconsulting.com

(949) 588-9800 x15

Steve Hendricks
MCSD, MCDBA
Data Matrix

shendricks@afsconsulting.com
(949) 588-9800 x15