Lots of people know the answer to this puzzle, but I am sure there are many who do not know the answer of the same as well. Here is the puzzle again for those who have vacation for the Holidays!
Here is the first script:
-- Decimal is rounded up DECLARE @MyValue DECIMAL(10,2) SET @MyValue = 10000/17 SELECT @MyValue DecResult GO
Here is the second script:
-- Decimal is not rounded up DECLARE @MyValue DECIMAL(10,2) SET @MyValue = 10000/17.0000 SELECT @MyValue DecResult GO
Here is the question –Â When we execute the first script the decimal’s are rounded up and when we execute the second script the decimal’s are not rounded up. WHY?
Just leave your answer in the comments field. I will select two random winners in the first week of January and will send surprise gift worth USD 29.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
48 Comments. Leave new
Int / int = int but int / float = float. This is the reason.
Thank you.
The first example is doing integer division, which does not round up. The second example is doing decimal division, which will round up.
The first example is performing integer division which will not round up, while the second example is performing decimal division (because at least one of the numbers is a decimal) which will round up.
Hi ,
Since 17 is integer value the decimals are rounded off to the nearest place. If we divide the same by the float value we will get the result as an float value.
I think when denominator are int then its rounded up.So result is cast to int .So datatype of output depend upon denominators.So when denominator is decimal output is decimal .
But upto how much decimal place output should calculate it depend upon numerator .
if both NUMERATOR and DENOMINATOR belong to NATURAL NUMBER DATATYPE (that does not support decimal – like tinyint, int, bigint …) the RESULT is automatically Rounded Up other wise not.
Division of two integer value return integer value. If we store that value in decimal variable then it just add that number of zeros after decimal place. If one or both of the value of division operation is decimal then it will return actual division result and stored in the decimal variable rounding value after declared decimal place.
Main thing is that this is not ’rounded up’ but this is ’rounded down’ or ’rounded off’ or ‘truncation’
e.g
SELECT 10/3.0,10/3,10/6.0,10/6
output:
——– — ——– —
3.333333 3 1.666666 1
if both operands of the division operator are integers, SQL Server will perform an integer division,
i.e. the result of the division would be ’rounded down’ to the nearest integer
–>–This is integer division by integer (This will give integer output: 588.00)
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000/17
SELECT @MyValue DecResult
GO
–>–At least one operands is non integer (This will give decimal output: 588.24)
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000/17.0000
SELECT @MyValue DecResult
GO
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000.00/17
SELECT @MyValue DecResult
GO
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000.00/17.00
SELECT @MyValue DecResult
GO
When multiplying two integers, a fraction will never occur. Therefore a fraction-handling methodology is assume same in division.
Main thing is that this is not ’rounded up’ but this is ’rounded down’ or ’rounded off’ or ‘truncation’
e.g
SELECT 10/3.0,10/3,10/6.0,10/6
output:
——– — ——– —
3.333333 3 1.666666 1
if both operands of the division operator are integers, SQL Server will perform an integer division,
i.e. the result of the division would be rounded down to the nearest integer
–>–This is integer division by integer (This will give integer output: 588.00)
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000/17
SELECT @MyValue DecResult
GO
–>–At least one operands is non integer (This will give decimal output: 588.24)
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000/17.0000
SELECT @MyValue DecResult
GO
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000.00/17
SELECT @MyValue DecResult
GO
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000.00/17.00
SELECT @MyValue DecResult
GO
When multiplying two integers, a fraction will never occur. Therefore a fraction-handling methodology is assume same in division.
In the first statment the compiler only divides two integers and then convert the result 588(int) to decimal data type.
While in second select stament first the compiler converts the integer 1000 to 1000.00 I:e to decimal and divides by decimal which thus results in decimal
Division of 2 integers is integer
And Division of integer and decimal/float is decimal itself
First Script give decimal representation of integer value(conversion)
Second script return decimal output with specified precision.(10,2)
.
my statements will be more clear if we try below queries-
SELECT 10000.00/17 DecResult
or
SELECT 10000/17.00 DecResult
and
SELECT 10000/17 DecResult
decimal value get rounded up when we specify precision as (10,2)
1) in first script decimals are rounded up because variable @MyValue contains division result of whole numbers. so the value 588 appended by .00 as we have scale up to 2 digits.
2) in second script it is not rounded up because in set statement the denominator is decimal number having value up to 4 decimal places. so the division result will also be a decimal number and will incorporate the actual result in decimal values up to 2 places as per scale defined in DECLARE statement.
Hi Pinal,
In First script ‘Integer division’ is used and following rules are applied
-The division rounds the result towards zero, and the absolute value of the result is the largest possible integer that is less than the absolute value of the quotient of the two operands.
And in 2 script ‘Floating-point division’ is used so it gives exact value for scale=2.
Thanks,
Vivek
In the first script, we use integer division (integer divided by integer) we always get an integer answer.
In the second script, an integer divided by decimal, so the result is a decimal
In the first script, it’s an integer division (integer divided by integer) we always get an integer answer.
In the second script, an integer is divided by decimal so the result is a decimal
In the first case
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000/17
SELECT @MyValue DecResult
GO
10000/17
both numerator and denominator are integer values so the result is 588
you are assigning 588 to a decimal (@MyValue ) variable.
So the out put it is returning 588.00
in second case
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000/17.00
SELECT @MyValue DecResult
GO
10000/17.00
numerator is integer and denominator value is float
integer/float results float.
So the out put it is returning 588.24
here also you are assigning to decimal. so the out put is coming the 588.24 the actual value….
please let me know incase i mistaken..
Thanks in advance…
Regards
Naga srinivasa Raja Sekhar p
nagasrinivasa,peri@gmail.com
Hi,
1. decimal / int = decimal
2. Int / decimal = decimal
3. Decimal / Decimal = Decimal
4 Int / Int = Int
In the first query division will return int values but since type of variable is decimal so it returns in decimal format. On the other side, in second query the division will return decimal value because of INT/Decimal. And rounded up to two decimal places because of its data type decimal (10,2), two digits after the decimal point.
If numerator Or denominator value are in decimal then result will not be rounded.
if both are integer then result will be rounded
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000.00/17
SELECT @MyValue DecResult
GO
Above code also will not be rounded.
Hi,
The first thing int/int results integer value, if it has fractional part,that will be truncated. Hence we are using decimal datatype with scale 2 ,so adding two 0’s .
Second query divided by floating value ,it results float value only. since we are using decimal data type with precision 2 , so the maximum number of decimal digits that can be stored to the right of the decimal point is 2.