March 31, 2008 at 1:31 am
HELLO,
trying to fetch float value .75 but select 3/4 always give me 0, any idea how to achive this.
thanks and Regards
sachin
March 31, 2008 at 1:36 am
March 31, 2008 at 3:34 am
i think 3/4 - SQL treat both as integer and won't return result but when we work with 3.0/4.0 or below convert it works fine,
select (convert(decimal(2,1),3)/convert(decimal(2,1),4));
March 31, 2008 at 7:02 am
I have a little sample demonstrating what's needed for these things to work.
use [TEST]
GO
create table #temp (MyNumber int)
INSERT INTO #temp
Select 2
union all select 8
union all select 10
--integer division truncates any floating point portion of the calc
Select Mynumber, 4 / MyNumber as calculation
from #temp
--we only need one side of the equation to be a floating point for SQL Server to cast
--option 1, if dealing with literals, make it a floating point number
Select Mynumber, 4.0 / MyNumber as calculation
from #temp
--option 2, if dealing only with columns, cast
Select Mynumber, 4 / cast(MyNumber as float) as calculation
from #temp
drop table #temp
Now for the gurus out there, does it matter which side you do the casting on? I've usually done option 2 in most of my code, but what is strange is that even though the results are the same numerically for option 1 and 2, the display window gives me a different number of trailing zeroes. If I replace the 4.0 with cast(4 as float) in number 1, I get the same thing.
The question is: what data type is 4.0?
March 31, 2008 at 7:37 am
Nice example, Jeremy and it doesn't work because of an implicit conversion of the answer to int, the data types of the inputs.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 31, 2008 at 11:57 am
When I tried it, I got 12 decimal places with a literal of "4.0". So, it appears to be decimal(18,12). But that's just my best guess.
As far as which is "better", I prefer to cast to float instead of forcing a specific decimal format or an implicit decimal format. But that's simply because at the lowest level, all multiplication, division, etc., on a computer is done as floating points. So it really boils down to "use what works best for the situation at hand".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2008 at 10:26 am
Let me get this straight:
You divide two integers and expect a decimal result ?
The previous suggestion:
[font="Courier New"]. select (convert(decimal(2,1),3)/convert(decimal(2,1),4))[/font]
does produce what you need:
[font="Courier New"].750000[/font]
So I am not clear on what the problem is.
April 2, 2008 at 3:22 am
Steve Jones - Editor (3/31/2008)
Nice example, Jeremy and it doesn't work because of an implicit conversion of the answer to int, the data types of the inputs.
no steve it is working
and the simple answer for the question is
last example with cast function either side
thanks
shamsudheen
April 3, 2008 at 4:17 pm
both of these work for me, and return .75
SELECT cast((3.0/4.0) as float)
SELECT cast(3 as float)/cast(4 as float)
April 4, 2008 at 12:58 am
Ivanna Noh (4/3/2008)
both of these work for me, and return .75
SELECT cast((3.0/4.0) as float)
SELECT cast(3 as float)/cast(4 as float)
hi noh
you are right , in your second example instead of putting cast in both side you can reduced to either side.
April 4, 2008 at 6:44 am
Ivanna Noh (4/3/2008)
both of these work for me, and return .75
SELECT cast((3.0/4.0) as float)
SELECT cast(3 as float)/cast(4 as float)
The first SELECT is NOT using only one CAST operation.
Since the original data was 3 and 4, CAST((3/4) AS FLOAT) produces 0.0
The only practical way to go from 3 / 4 to 3.0 / 4.0 requires two cast operations, as outlined in the second SELECT statement. And at that point, since you are dividing 3.0 by 4.0, there is no reason to CAST(0.75 AS FLOAT).
So, assuming that you are dividing an INTEGER column by anouther INTEGER column (instead of hard-coded numbers), in order to obtain a float you have to cast the result.
The question still stands: what is the design reason to expect a float-type result from an integer-truncated division ?
Regards
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply