|
Home > Archive > Microsoft SQL Server forum > April 2006 > float numbers
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| homa@havet.se 2006-04-03, 9:30 am |
| Hi!
How do I do to make t-sql not rounding the result that i returned?
For example:
0.9616458*60 = 57,698748 (in any calculator)
while following:
--------------------------------
declare @a float
declare @b int
set @a=0.9616458
set @b=60
print @a*@b
---------------------------------
will show :57.6987
How do I do to make MSSQL to show me the value whothout rounding it?
Thanks!
| |
| Tom Moreau 2006-04-03, 11:27 am |
| Use SELECT:
select @a*@b
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<homa@havet.se> wrote in message
news:1144073554.334910.187210@g10g2000cwb.googlegroups.com...
Hi!
How do I do to make t-sql not rounding the result that i returned?
For example:
0.9616458*60 = 57,698748 (in any calculator)
while following:
--------------------------------
declare @a float
declare @b int
set @a=0.9616458
set @b=60
print @a*@b
---------------------------------
will show :57.6987
How do I do to make MSSQL to show me the value whothout rounding it?
Thanks!
| |
| homa@havet.se 2006-04-03, 11:27 am |
| Hi! Itried your code and it works fine in the example I gave but not
when I implement it in my code:
declare @x float(53)
declare @grades int
declare @minutes int
declare @seconds float(53)
declare @minwithrest float(53)
declare @leftover float(53)
select @x = 57.66602743
select @grades = floor(@x)
select @leftover = @x-@grades
select @minwithrest = (@leftover * 60)
select @minutes = floor(@minwithrest)
select @seconds = (@minwithrest - @minutes) * 60
print @grades
print @minutes
print @seconds
gives me 57.6987 as @seconds while it should be 57,698748
thanks!
| |
| homa@havet.se 2006-04-03, 11:27 am |
| sorry. I meant that it gives me 57.698747999999114 and It should be
57,698748 .
(The print is replaced by select)
| |
| gabe101 2006-04-03, 11:27 am |
|
I ran the 2nd code you've posted and I get the 57.6987 result
| |
| Tom Moreau 2006-04-03, 11:27 am |
| Again, use SELECT:
select @seconds
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<homa@havet.se> wrote in message
news:1144077300.519079.316740@t31g2000cwb.googlegroups.com...
Hi! Itried your code and it works fine in the example I gave but not
when I implement it in my code:
declare @x float(53)
declare @grades int
declare @minutes int
declare @seconds float(53)
declare @minwithrest float(53)
declare @leftover float(53)
select @x = 57.66602743
select @grades = floor(@x)
select @leftover = @x-@grades
select @minwithrest = (@leftover * 60)
select @minutes = floor(@minwithrest)
select @seconds = (@minwithrest - @minutes) * 60
print @grades
print @minutes
print @seconds
gives me 57.6987 as @seconds while it should be 57,698748
thanks!
| |
| gabe101 2006-04-03, 11:27 am |
| At this point, I'm confused about what result you are looking for. Use
convert(decimal(X,Y)
,@seconds) to fine-tune the result you need. X can
be as large as 38 places and Y is your tunable decimal size.
HTH,
Gabe
| |
| Tom Moreau 2006-04-03, 11:27 am |
| Try:
select
cast (@seconds as numeric (8, 6))
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<homa@havet.se> wrote in message
news:1144077414.697728.270000@v46g2000cwv.googlegroups.com...
sorry. I meant that it gives me 57.698747999999114 and It should be
57,698748 .
(The print is replaced by select)
| |
| Tom Moreau 2006-04-03, 11:27 am |
| I get:
57.698748
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"gabe101" <gabe101@gmail.com> wrote in message
news:1144078109.143813.199680@z34g2000cwc.googlegroups.com...
I ran the 2nd code you've posted and I get the 57.6987 result
| |
| gabe101 2006-04-03, 11:27 am |
| Tom,
Why do you suppose that is? Is it our versions? I'm on 8.00.760...
| |
| Tom Moreau 2006-04-03, 11:27 am |
| I'm on 8.00.818, which is the post-SP3a security hotfix:
http://support.microsoft.com/kb/821277
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"gabe101" <gabe101@gmail.com> wrote in message
news:1144079629.602630.102500@u72g2000cwu.googlegroups.com...
Tom,
Why do you suppose that is? Is it our versions? I'm on 8.00.760...
| |
| gabe101 2006-04-03, 11:27 am |
| Something else that is puzzling me about all of this:
Put 'select @leftover' at the end of this code.
I get:
0.66602742999999975 as the @leftover value. Shouldn't that be
0.66602743........why did Query Analyzer do this?
If floor(@x) = 57
and
@Leftover = 57.66602743 - @x then the result should be 0.66602743
| |
| Tom Moreau 2006-04-03, 1:30 pm |
| I get:
0.66602742999999975
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"gabe101" <gabe101@gmail.com> wrote in message
news:1144080712.526308.302260@e56g2000cwe.googlegroups.com...
Something else that is puzzling me about all of this:
Put 'select @leftover' at the end of this code.
I get:
0.66602742999999975 as the @leftover value. Shouldn't that be
0.66602743........why did Query Analyzer do this?
If floor(@x) = 57
and
@Leftover = 57.66602743 - @x then the result should be 0.66602743
| |
| homa@havet.se 2006-04-03, 1:30 pm |
| I can't use the convert since I don't know how many decimals to round
the result to and for this function that I'm writing the result must be
exact. This is a GPS conversion formula and if I round things always
with fir examole 6 decimals then the x,y coordinats will be wrong for
the GPS system. In my example above I need the result to be exactley
57,698748 and not 57.6987 or 57.698747999999114 .... Since I don't
know the amount of decimlas returned I cant use the convert..
| |
| gabe101 2006-04-03, 1:30 pm |
|
> Since I don't
> know the amount of decimlas returned I cant use the convert..
This leads to another question...what about 1 divided by 3. What will
you do with this result?
Gabe
| |
| homa@havet.se 2006-04-03, 8:26 pm |
| very interesting..
I don't know...
| |
| Erland Sommarskog 2006-04-03, 8:26 pm |
| (homa@havet.se) writes:
> I can't use the convert since I don't know how many decimals to round
> the result to and for this function that I'm writing the result must be
> exact.
In such case you should not use float. Float gives you approxamite numbers.
Float values consists of a 53-bit number with a mantissa. This permits
for a broad range of value, to the price of approxamite precision.
The given example:
declare @a float
declare @b int
set @a=0.9616458
set @b=60
print @a*@b
select @a*@b
Gives you 57.6987 for the print, because there is an implicit conversion
to string in SQL Server.
The SELECT statement returns a binary float value to the client, so it
up to the client how it is presented. In Query Analyzer I get
57.698748000000002, whereas in Management Studio that ships with
SQL 2005, I get 57,698748. ISQL, the command-line tool that uses
DB-Library to connect returns 57.698748.
> This is a GPS conversion formula and if I round things always
> with fir examole 6 decimals then the x,y coordinats will be wrong for
> the GPS system. In my example above I need the result to be exactley
> 57,698748 and not 57.6987 or 57.698747999999114 .... Since I don't
> know the amount of decimlas returned I cant use the convert..
If you don't know the decimals of your result before hand, you will
have to convert the result to a string, and then try to guess how many
decimals you really have.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
|
|
|
|
|