CODEWITHSUNDEEP
sql-serversql-server-2008divisionrounding
UnDiUdin
UnDiUdin

Reputation: 15374

How to get a float result by dividing two integer values using T-SQL?

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?

Upvotes: 265

Views: 485613

Answers (10)

Yair Maron
Yair Maron

Reputation: 1948

When using literals, the best way is to "tell" SQL which type you mean.

if you want a decimal result, add decimal point ".0" to your numbers:

SELECT 1.0 / 3.0
Result
0.333333

if you want a float (real) result, add "e0" to your numbers:

SELECT 1e0 / 3e0
Result
0.333333333333333

Upvotes: 0

samsamara
samsamara

Reputation: 4750

I was surprised to see select 0.7/0.9 returning 0.8 in Teradata given they're already as floats/decimal numbers! I had to do cast(0.7 as float) to get the output that I was after.

Upvotes: 1

Supreme Dolphin
Supreme Dolphin

Reputation: 2584

I understand that CASTing 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.

Upvotes: 20

trojan
trojan

Reputation: 1535

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

Upvotes: 3

nazmul.3026
nazmul.3026

Reputation: 998

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.

Upvotes: 3

M.S.
M.S.

Reputation: 4413

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)

Upvotes: 65

Troglo
Troglo

Reputation: 1642

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

Upvotes: 10

Richard
Richard

Reputation: 30618

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)

Upvotes: 407

xiaowl
xiaowl

Reputation: 5207

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

Upvotes: 92

stb
stb

Reputation: 3503

use 

select 1/3.0

This will do the job.

Upvotes: 35

Related Questions