How to get a float result by dividing two integer values using T-SQL?
Sql ServerSql Server-2008DivisionRoundingSql Server Problem Overview
Using T-SQL and Microsoft SQL Server I would like to specify the number of decimal digits when I do a division between 2 integer numbers like:
select 1/3
That currently returns 0
. I would like it to return 0,33
.
Something like:
select round(1/3, -2)
But that doesn't work. How can I achieve the desired result?
Sql Server Solutions
Solution 1 - Sql Server
The suggestions from stb and xiowl are fine if you're looking for a constant. If you need to use existing fields or parameters which are integers, you can cast them to be floats first:
SELECT CAST(1 AS float) / CAST(3 AS float)
or
SELECT CAST(MyIntField1 AS float) / CAST(MyIntField2 AS float)
Solution 2 - Sql Server
Because SQL Server performs integer division. Try this:
select 1 * 1.0 / 3
This is helpful when you pass integers as params.
select x * 1.0 / y
Solution 3 - Sql Server
It's not necessary to cast both of them. Result datatype for a division is always the one with the higher data type precedence. Thus the solution must be:
SELECT CAST(1 AS float) / 3
or
SELECT 1 / CAST(3 AS float)
Solution 4 - Sql Server
use
select 1/3.0
This will do the job.
Solution 5 - Sql Server
I understand that CAST
ing to FLOAT
is not allowed in MySQL and will raise an error when you attempt to CAST(1 AS float)
as stated at MySQL dev.
The workaround to this is a simple one. Just do
(1 + 0.0)
Then use ROUND
to achieve a specific number of decimal places like
ROUND((1+0.0)/(2+0.0), 3)
The above SQL divides 1 by 2 and returns a float to 3 decimal places, as in it would be 0.500
.
One can CAST
to the following types: binary, char, date, datetime, decimal, json, nchar, signed, time, and unsigned.
Solution 6 - Sql Server
Looks like this trick works in SQL Server and is shorter (based in previous answers)
SELECT 1.0*MyInt1/MyInt2
Or:
SELECT (1.0*MyInt1)/MyInt2
Solution 7 - Sql Server
Use this
select cast((1*1.00)/3 AS DECIMAL(16,2)) as Result
Here in this sql first convert to float or multiply by 1.00 .Which output will be a float number.Here i consider 2 decimal places. You can choose what you need.
Solution 8 - Sql Server
If you came here (just like me) to find the solution for integer value, here is the answer:
CAST(9/2 AS UNSIGNED)
returns 5