Member Avatar for 330m

Hi All,

When I run the query:

select (100/50)

It give me "2"... good.

But when I run the query:

select (50/100)

I was expected it will give me 0.5... but it gives me 0 instead? Why? and How can I get the "0.5"?

  • 9 Contributors
  • 10 Replies
  • 2K Views
  • 3 Years Discussion Span
  • Latest Post Latest Post by peter_budo

Dani AI

Generated

You are seeing integer division. In T-SQL, if both operands are integers, SQL Server does the math in integer arithmetic and truncates the remainder, so 50/100 becomes 0. called out data types, which is the root cause. You do not need to move the calculation into application code as suggested by ; just make at least one operand a decimal so SQL Server promotes the calculation.

These patterns avoid integer division without changing your schema:

-- Force decimal arithmetic with an explicit CAST on one side
SELECT 50 / CAST(100 AS decimal(10,2)) AS result;     -- 0.5

-- When dividing columns, cast and protect against divide-by-zero
SELECT CAST(a AS decimal(18,4)) / NULLIF(b, 0) AS ratio
FROM dbo.YourTable;

-- If you need a fixed number of decimals in the final output
SELECT CAST(CAST(a AS decimal(18,6)) / NULLIF(b, 0) AS decimal(18,2)) AS ratio2
FROM dbo.YourTable;

Notes:

  • Using a decimal/ numeric type keeps results exact. That is safer for money or percentages than float (approximate), even though and are right that making one operand non-integer fixes the immediate issue.
  • Pick precision/scale that fit your data. A common default is decimal(18,2) for currency, or more scale (e.g., 4–6) if you need finer ratios.
  • Prefer explicit casts over relying on literal tricks; it is clearer to readers and prevents surprises when column types change.

Recommended Answers

All 10 Replies

Member Avatar for jwshepherd

change the resulting variable to type double

Member Avatar for bops

im not too familiar with sql but i would assume it has something to do with data types maybe? for example if it was integer it would only return a whole number such as 2 or 0 in your case, im not too sure about this in sql as i dont do it but generally that could be the problem

Member Avatar for jwshepherd

try doing your calculations before your sql statment

Dim i As Double
Dim MySql as string 
i = 50 / 100
Mysql="Select " & i & " FROM table;"
rs.open MySQl,conn,3,3,1
Member Avatar for eralper

Hi,

Try "select (50 * 1.0/100)" instead of "select (50/100)"

Eralper
http://www.kodyaz.com

Member Avatar for campkev

or simply "select 50. / 100"

Member Avatar for wujtehacjusz

It will work fine as long as one of the numbers in division is a float or similar.
Try

select (cast(50 as float)/100)

This will work with any numeric data.

Member Avatar for tesuji

oops wujtehacjusz, you may have a look at issue date;)

Member Avatar for wujtehacjusz

oops wujtehacjusz, you may have a look at issue date;)

Damn you are right... :P

Member Avatar for sujendra

the integer division is done. so the result is in integer.
try select convert(numeric(5,2), 50)/100 OR
select 50.00/100.00

Member Avatar for peter_budo

the integer division is done. so the result is in integer.
try select convert(numeric(5,2), 50)/100 OR
select 50.00/100.00

Another genius...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.