Decimals in integer operations

shreekar

OK - I have tried the below:

declare @d decimal(10,2)
select @d = 859 * 1/5
select @d

This returns 171.00 and

declare @d decimal(10,2)
select @d = 859.00 * 1/5.00
select @d

This returns 171.80 This tells me that the 2 numbers participating in the operation have to be decimals by definition. Is there any way to work around this as I do not want to change the column types to decimals as I do not expect them to hold fraction data any time. It is only the result that may have fraction data. Any ideas?

Shreekar

Lost User · declare @d decimal(10,2) select @d = 859.00 * 1/5.00 select @d

Cast[^] it;

SELECT CAST(859 AS DECIMAL) / CAST(5 AS DECIMAL)

I are Troll :suss:

shreekar · declare @d decimal(10,2) select @d = 859.00 * 1/5.00 select @d

Figured it out myself, I cast the operands themselves to decimals and the product was decimal. Sorry about that - turned out to be loud thinking than a genuine question.

Shreekar

Luc Pattyn

in most programming languages, when all numbers are integer, division will truncate. You can promote a (sub)expression to floating-point in several ways: by inserting a floating constant, by having at least one floating variable, by casting, by converting. As a simple experiment, I would prefix 1.0* to whatever expression you are having. Warning: this rule also applies to subexpressions, so 2/5 will be zero, whereas 1.0*2/5 would not! :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

shreekar

Just missed your post. Realised the same solution as you suggested. Thanks for the confirmation!

Shreekar

David Skelly · declare @d decimal(10,2) select @d = 859.00 * 1/5.00 select @d

declare @n integer
declare @d decimal(10,2)
select @n = 859
select @d = CAST(@n AS decimal(10,2)) / 5
select @d

Returns 171.80

David Skelly

It's like buses. You stand around waiting for an answer, and then three come along all at the same time.

Lost User

You're welcome. Thinking out loud helps, even on a messageboard :)

I are Troll :suss:

shreekar

Multiplying by 1.0 is definitely easier on the eyes than multiple nested cast statements and produces the same output - at least in my case because there is only one operation. So thanks for that. Earlier, I had read posts alluding to this technique but I had missed the point - now I get it.

Shreekar

Luc Pattyn

you're welcome. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

David Skelly

One word of warning with this technique, the following give different results:

859 / 5 * 1.0
1.0 * 859 / 5

If you go down this route, you will need to understand how SQL Server applies its rules for implicit datatype conversion.