select 3/4

Question

Post reply

select 3/4

Sachin Butala-182900

SSC-Addicted

Points: 406
More actions
March 31, 2008 at 1:31 am

#204834

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Try this:

select 3.0/4.0

Karl
source control for SQL Server

Sachin Butala-182900 SSC-Addicted Points: 406 More actions · Answer 2

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));

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 3

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?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

Nice example, Jeremy and it doesn't work because of an implicit conversion of the answer to int, the data types of the inputs.

GSquared SSC Guru Points: 260824 More actions · Answer 5

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

J-440512 SSCertifiable Points: 6285 More actions · Answer 6

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.

shamshudheen SSCrazy Points: 2390 More actions · Answer 7

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

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 8

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)

shamshudheen SSCrazy Points: 2390 More actions · Answer 9

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.

J-440512 SSCertifiable Points: 6285 More actions · Answer 10

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