asked
Invalid result when dividing with numerator less than the denominator
Invalid results when dividing with a numerator less than the denominator. I am using SQL Server 2008R2 Select 155/156, 4/6, 6/2, 6/4 Returns 0, 0, 3, 1 Does anyone know why integer results are being returned and HOW TO FIX !!!??? Thanks, Mary
This behaviour is by design, and the results are valid because your numbers are integers, so you get integer division. If you need higher accuracy then you can cast any of the numbers as decimal. For example, select 155 / 156; returns 0, but select cast(155 as decimal(10, 2)) / 156; returns 0.993589 Another possibility to force the scale to be 6 digits is to use the cave man method of multiplying the first number by 1.0 or optionally by appending .0 to the first number, i.e. select 1.0 * 155/156 method1, 155.0 / 156 method2; -- results method1 method2 --------- --------- 0.993589 0.993589 If you need a smaller scale then you can first multiply the first number by 1.0 but then cast the result as decimal of smaller scale, for example select cast((155.0 / 156) as decimal(10, 2)) result; --returns result ------- 0.99 The [Precision, Scale and Length (Transact SQL)][1] article in BOL gives an excellent overview of the effect of the operations on the results' precision and scale. Oleg [1]:
http://msdn.microsoft.com/en-us/library/ms190476.aspx
