By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

float numbers

P: n/a
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!

Apr 3 '06 #1
Share this Question
Share on Google+
16 Replies


P: n/a
Use SELECT:

select @a*@b

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<ho**@havet.se> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.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!

Apr 3 '06 #2

P: n/a
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!

Apr 3 '06 #3

P: n/a
sorry. I meant that it gives me 57.698747999999114 and It should be
57,698748 .
(The print is replaced by select)

Apr 3 '06 #4

P: n/a


I ran the 2nd code you've posted and I get the 57.6987 result

Apr 3 '06 #5

P: n/a
Again, use SELECT:

select @seconds

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<ho**@havet.se> wrote in message
news:11**********************@t31g2000cwb.googlegr oups.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!

Apr 3 '06 #6

P: n/a
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

Apr 3 '06 #7

P: n/a
Try:

select
cast (@seconds as numeric (8, 6))
--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<ho**@havet.se> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...
sorry. I meant that it gives me 57.698747999999114 and It should be
57,698748 .
(The print is replaced by select)

Apr 3 '06 #8

P: n/a
I get:

57.698748
--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"gabe101" <ga*****@gmail.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.com...
I ran the 2nd code you've posted and I get the 57.6987 result

Apr 3 '06 #9

P: n/a
Tom,

Why do you suppose that is? Is it our versions? I'm on 8.00.760...

Apr 3 '06 #10

P: n/a
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" <ga*****@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
Tom,

Why do you suppose that is? Is it our versions? I'm on 8.00.760...

Apr 3 '06 #11

P: n/a
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

Apr 3 '06 #12

P: n/a
I get:

0.66602742999999975
--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"gabe101" <ga*****@gmail.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.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

Apr 3 '06 #13

P: n/a
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..

Apr 3 '06 #14

P: n/a
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

Apr 3 '06 #15

P: n/a
very interesting..

I don't know...

Apr 3 '06 #16

P: n/a
(ho**@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 3 '06 #17

This discussion thread is closed

Replies have been disabled for this discussion.