convert
-
how to convert a big float value to varchar
declare @flt float
select @flt = 500000000000.845
select convert(varchar, @flt)
gives me an answer 5e+011
even if I put the style in the convert statement it still gives me an e value
can anyone help me
-
Any reasons for float?
declare @flt decimal(19,3)
select @flt = 500000000000.845
select convert(varchar, @flt)
------------------------------
500000000000.845
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The problem is the number of decimals (digits after decimal) are not fixed and I do not want trailing Zeros
How do I avoid that
-
How do you get trailing 0s in a numeric column?
A look at the table in EM should not show such thing, a linked table in Access neither. Don't get confused when looking at the data in QA. Your front-end should do the job for you.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
First a caveat - variable type float only has 16 digit (or so) precision (corresponds to a 6 byte mantissa). Even worse, decimals are not stored precisely as typed as there is a conversion from "decimal fraction" to "binary (or hexadecimal) fraction" going on under the covers. If you want to keep the precision, avoid using "float".
The following bit of code may give you some ideas. It works for positive numbers only ... requires tweaking for negative numbers. It's intriguing to watch the values as the number of digits in the number is increased!
declare @flt float,
@bign numeric(38,0), -- biggest allowed value
@bigv varchar(50)
select @flt = 5999999923.568
select @bign = floor(@flt)
select @bigv = convert(varchar,@bign)+substring(convert(varchar,@flt-@bign),2,99)
select @flt,convert(varchar,@flt),@bigv,convert(varchar,@bign)+substring(convert(varchar,@flt-@bign),2,99)
5999999923.5679998 6e+009 5999999923.568 5999999923.568
-
Yes, that's another big issue!
Float and real are imprecise data types. That's their nature. I think they are relicts from the times times where storage space was a huge issue. If you need high precision the only way is use decimal. Ad hoc there is only one use for float, that is when you have to deal with really huge numbers like in astronomy.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You've reminded me of another issue, one which I learned very early in my career - which was in the days when storage was an issue (programming for an IBM System/7 and having to keep the code and data structure under 6K words [=12KB]).
If you intend summing or aggegating a field, eg to keep a production total (which Codd disapproves of anyway!), NEVER use float. As the sum proceeds you keep losing more and more precision of the field you are adding!
-
This also is likely to happen when you have complex calculations to do. I have seen mainframe listing using log() and ln() stuff just to work around this impreciseness.
Fortunately we now have decimal. How lucky we are
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
but this does not provide any ans to her,
i could have answered,
but i dont know.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
I don't think we haven't provided an answer. Unless you don't use really huge numbers, you can workaround using decimal instead of float. And I don't think there is an issue with trailing 0s at all.
So I think the question is answered, isn't it?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply