Integer division in sql server
Sql Server-2005Sql Server-2005 Problem Overview
In Microsoft SQL Server 2005, why do the following commands produce integer results?
SELECT cast(151/6 AS DECIMAL(9,2))
SELECT 151/6
Sql Server-2005 Solutions
Solution 1 - Sql Server-2005
In the first you are getting the result of two integers and then casting the result as DECIMAL(9,2). In the second you're just dividing two integers and that's expected.
If you cast one of the integers as a decimal BEFORE you do the division, you'll get a decimal result.
SELECT 151/CAST(6 AS DECIMAL (9,2))
Solution 2 - Sql Server-2005
Yes that is standard behavior
do
SELECT 151/6.0
or
SELECT 151/(CONVERT(DECIMAL(9,2),6))
or
SELECT 151/(6 * 1.0)
Solution 3 - Sql Server-2005
Because 151 and 6 are integers and you are doing integer division, even before the cast.
You need to make sure at least one of the arguments is a float type:
SELECT 151.0/6
Or
SELECT 151/6.0
Solution 4 - Sql Server-2005
Not a direct answer to your question. Still worth to take a look at Operators in Expressions if you need this in SSRS
> / Divides two numbers and returns a floating-point result. > > \ Divides two numbers and returns an integer result. > >Mod Returns the integer remainder of a division.
Solution 5 - Sql Server-2005
You need to give a placeholder for decimal places as well
Example
SELECT 151.000000/6
OR
SELECT 151/6.000000
Both will produce
25.16666666
Solution 6 - Sql Server-2005
For the same reason they would in C#, Java and other mainstream languages.
In integer arithmetic, the CAST is after the maths...
Solution 7 - Sql Server-2005
The CAST statement is a bit verbose. You can use the following instead:
DECLARE @TO_FLOAT FLOAT = 1.0;
SELECT (1 * @TO_FLOAT) / 2;
Or use a different multiplier type like DECIMAL if you prefer.
Solution 8 - Sql Server-2005
Try this:
SELECT 1.0*cast(151/6 AS DECIMAL(9,2))
SELECT 1.0*151/6