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!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.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!|||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!|||sorry. I meant that it gives me 57.698747999999114 and It should be
57,698748 .
(The print is replaced by select)|||
I ran the 2nd code you've posted and I get the 57.6987 result|||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.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!|||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|||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.googlegr oups.com...
sorry. I meant that it gives me 57.698747999999114 and It should be
57,698748 .
(The print is replaced by select)|||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.googlegr oups.com...
I ran the 2nd code you've posted and I get the 57.6987 result|||Tom,
Why do you suppose that is? Is it our versions? I'm on 8.00.760...|||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.googlegr oups.com...
Tom,
Why do you suppose that is? Is it our versions? I'm on 8.00.760...|||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|||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.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|||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..|||> 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|||very interesting..
I don't know...|||(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
没有评论:
发表评论