Decimals in integer operations
-
Hi Database: SQL Server 2005 select 589/5 produces 117 How can I make it return 117.8? I am doing the division operation on 2 int columns and want the output in decimals. Do I have to make the columns themselves decimal? Thanks
Shreekar
OK - I have tried the below:
declare @d decimal(10,2)
select @d = 859 * 1/5
select @dThis returns 171.00 and
declare @d decimal(10,2)
select @d = 859.00 * 1/5.00
select @dThis 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
-
OK - I have tried the below:
declare @d decimal(10,2)
select @d = 859 * 1/5
select @dThis returns 171.00 and
declare @d decimal(10,2)
select @d = 859.00 * 1/5.00
select @dThis 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
-
OK - I have tried the below:
declare @d decimal(10,2)
select @d = 859 * 1/5
select @dThis returns 171.00 and
declare @d decimal(10,2)
select @d = 859.00 * 1/5.00
select @dThis 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
-
Hi Database: SQL Server 2005 select 589/5 produces 117 How can I make it return 117.8? I am doing the division operation on 2 int columns and want the output in decimals. Do I have to make the columns themselves decimal? Thanks
Shreekar
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, so2/5will 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.
-
OK - I have tried the below:
declare @d decimal(10,2)
select @d = 859 * 1/5
select @dThis returns 171.00 and
declare @d decimal(10,2)
select @d = 859.00 * 1/5.00
select @dThis 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
-
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
It's like buses. You stand around waiting for an answer, and then three come along all at the same time.
-
Just missed your post. Realised the same solution as you suggested. Thanks for the confirmation!
Shreekar
-
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, so2/5will 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.
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
-
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
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.
-
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
One word of warning with this technique, the following give different results:
859 / 5 * 1.0
1.0 * 859 / 5If you go down this route, you will need to understand how SQL Server applies its rules for implicit datatype conversion.