Problem in using Float data type

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem in using Float data type

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-10-06 : 09:14:43

When I insert the value in a column having float data type, it adds trailing values. Lets suppose I inserted the value 1.89 and when I retrieves it in Query Analyzer, it displays it like this 1.8899999999999999.

Please tell me what I’ll have to do if I want to get the same value that I have inserted i.e., 1.89

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-10-06 : 09:31:53

Do you really need to use float as your data type?! It is inaccurate and not the most efficient data type to use. Computers work with integers and they only represent real numbers through complex calculations.

Most floating point numbers a computer can represent are just approximations. Anyway, I don't have much information on this but maybe you ought to read up on the properties of real numbers.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-10-06 : 09:35:03

Ok if I may use numeric or decimal data type it adds trailing zeros. Like I inserted the value 1.89 and upon retrieval it displays 1.890000

I want to get the actual value, how is it possible. Please let me know

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-10-06 : 09:38:55

Sure.... do you know the number of decimal places you are going to have?! If it is going to be two then do something like this :


DECLARE @number numeric(20,2)
SET @number = 1.89
SELECT @number

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-10-06 : 09:44:17

the decimal places could be 2 or 10 so we may fix it to some where 6.

now the values inserted could be
1
1.89
1.823
1.8123456
1.2

and the result after retrieval is
1.000000
1.890000
1.823000
1.812346
1.200000

which is not acceptable, as we want actual values in the result which were inserted

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-10-06 : 09:58:26

quote:

and the result after retrieval is
1.000000
1.890000
1.823000
1.812346
1.200000

which is not acceptable, as we want actual values in the result which were inserted

How is the data retrieved? Are you concerned as to how SQL Server stores these numbers in your table or is it the way it displays them? If this is a display issue then you could always convert the values to varchar (with added manipulation to remove the zeros). Could you clarify what you mean by retrieving?

you could always convert to varchar and do something like :


declare @number numeric(30, 10)
set @number = 10456.22653
select substring(convert(varchar, @number), 1, charindex('.', @number))+
	reverse(replace(substring(reverse(@number), 1, charindex('.', reverse(@number))-1), '0', '')) 
as 'New Format',@number as 'Oroginal format'

and if all this doesn't help, you could always do this through front end instead.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-10-06 : 10:08:35

but if user inserts

DECLARE @number numeric(20,6)
SET @number = 1.8900
select substring(convert(varchar, @number), 1, charindex('.', @number))+
reverse(replace(substring(reverse(@number), 1, charindex('.', reverse(@number))-1), '0', ''))
as 'New Format',@number as 'Oroginal format'

Then????

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-10-06 : 10:13:41

Well in that case it would make sense to store the values in a say, a VARCHAR data type column. You could always then use the convert function to manipulate the values.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources