What am I missing?
-
Hi !
This query should return 0.1, correct?
What am I missing?
DECLARE @p INT, @n INT
SET @p = 10
SET @n = 1
PRINT @n / @p
PRINT CAST( @n / @p AS DECIMAL(12,1))
Best Regards,
Rechousa,
V.N.Gaia
Portugal -
You are missing order of operations.
1) you do division. You divide int by int, result is int (see BOL) = 0
2) you do conversion of the division result to DECIMAL(12,1) = 0.0
_____________
Code for TallyGenerator -
Thank U Sergiy, it worked !
I have now changed the query to:
DECLARE @p INT, @n INT
SET @p = 10
SET @n = 1
PRINT CAST(@n AS DECIMAL(12,1)) / CAST(@p AS DECIMAL(12,1))
I didn't know that if I divide int by int, result is int :p
Thank U
Best Regards,
Rechousa,
V.N.Gaia
Portugal -
No, you've got it wrong.
Result of your expression is REAL.
As I understand it must be DECIMAL(12,1)
So, you need to do this:
CAST(CAST(@n AS FLOAT) / @p AS DECIMAL(12,1))
Check it out for @p = 3 and @n = 1.
_____________
Code for TallyGenerator -
Hi,
DECLARE @p decimal, @n decimal
SET @p = 10
SET @n = 1
PRINT Cast((@n / @p) as decimal(2,1))
Please try this
Shabbir Muntakhab
-
Float is
inpreciseapproximate and may throw off the expected results.If you want 2 inputs to be int the do as you have but do one of the followiung ways to get your expected result
PRINT CAST(CAST(@n as decimal(12,1))/CAST(@p as decimal(12,1)) as Decimal(12,1))
PRINT ROUND(CAST(@n as decimal(12,1))/CAST(@p as decimal(12,1)),1)
Or do as suggested and let your variables be decimals and you can do
PRINT CAST(@n /@p as Decimal(12,1))
PRINT ROUND(@n /@p,1)
-
Antares686, please take any reference book on math and read about precision in math operations.
Then you'll realise that result of calculations from FLOAT ALWAYS more precise than result of calculations from decimal(12,1).
Conversion to decimal(12,1) must be the last one, only for presentation purposes.
_____________
Code for TallyGenerator -
From SQL BOL and yes I used the wrong word.
Floating point data is approximate; not all values in the data type range can be precisely represented.
And here is a nice piece of info
-
Antares686, this tells about presentation mainly, again.
I tell you, read math references. Read about precision behind the scene.
After you've discovered the truth i'd probably thankful to MS for implicitly converting all DECIMAL values participating in division into FLOAT before performing division.
They don't tell you about it, but they actually do it.
_____________
Code for TallyGenerator -
Another point that i think found me is, I am not sure if that is relevant or not. But when running the script below:
declare @a float
declare @b-2 float
set @a= 10
set @b-2= 3
select @a/@b
Yeilds: 3.3333333333333335
declare @a decimal(15,1)
declare @b-2 decimal(15,1)
set @a= 10
set @b-2= 3
select @a/@b
Yeilds: 3.33333333333333
It seems that using decimal is not rounding off and when working with float does.
-
Oh boy... you sure about that?
FLOAT is based on BINARY math which is also why it's so fast. The problem with Binary math is that you sometimes need more bits than the computer has registers to successfully be able to represent a given decimal number.
I came to the conclusion that, if I can, I will always avoid using explicit FLOATs...
Consider/Execute the following and draw your own conclusions... do notice that the two code snippets are identical in every way except for the datatype of the variables...
DECLARE @One FLOAT
DECLARE @Ten FLOAT
SELECT @One = 1,
@Ten = 10
SELECT @One/@Ten
GO
DECLARE @One DECIMAL(10,1)
DECLARE @Ten DECIMAL(10,1)
SELECT @One = 1,
@Ten = 10
SELECT @One/@Ten
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes, I'm pretty sure.
Calculation based on FLOAT gives you 17 precise digits, calculation based on DECIMAL brings you 14 digits, all precise.
Comparing those numbers I conclude that 17 > 14 and FLOAT calculation is more precise than DECIMAL one.
Is something wrong with this conclusion?
_____________
Code for TallyGenerator -
Kinda... if you don't consider any type of rounding, the FLOAT 17 digit's in the example I gave came up with the wrong answer...
But, after re-reading your posts, I get what you're trying to say. Most folks don't realize, for example, that 15 digit calculators actually have 18 digits worth of computational power and the displayed result is rounded to 15 digits. Why do they do that? As pointed out earlier, binary adders are a bit quicker than decimal adders (employs a detect and early "carry" at 9 flipping to 0 instead of the full width of the adder) and they're certainly easier to build at the chip die level.
You may be very right... SQL Server may do the same thing with the DECIMAL datatypes and that's why they're a bit slower than FLOATs... the overhead of "rounding" is probably what takes the extra time...
Still, DECIMAL is very convenient for those that don't know that and forget to do the rounding at the desired precision. I probably won't change my preference unless I'm writing some SQL that's gonna do the calcs to land a Martian Lander or something like that
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply