473,326 Members | 2,102 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

float numbers

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
16 4648
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
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
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


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

Apr 3 '06 #5
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
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
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
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
Tom,

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

Apr 3 '06 #10
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
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
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
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
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
very interesting..

I don't know...

Apr 3 '06 #16
(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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Vio | last post by:
I need to test for equality between simple 2 decimal numbers. For example: if (10 + 15.99) == 25.99: do some stuff... The preceding sentence should be TRUE, but to Python it appears FALSE....
6
by: Nobody | last post by:
How does one round a float? ie... 4.4 returns 4, while 4.5 returns 5. I see the floor and ceiling functions, but that floor would take 4.4 and return 4 and ceiling would return 5. I guess I could...
6
by: malv | last post by:
Simple case: In this list, how to find all occurences of intervals of n adjacent indexes having at least one list-member with a value between given limits. Visualizing the list as a...
6
by: spooky | last post by:
Hi, I have a text box that holds floating numbers how do i format it so that the user will not be able to enter values greater than 99.99. I want to insert a '.' when the length of the...
4
by: buntyindia | last post by:
Hi, I have a calculator with seven textBox to add Float numbers upto 2 decimal: I have created following function in js: function total_expenses() { // reteriving all the values from...
6
by: krishnaneeraja | last post by:
Hi, Iam developing windows application using vb.net with c#.net.In this i want to validate textbox accept only negative float numbers like -2.3 etc.... please help me.
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.