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

Question

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

User · Accepted Answer

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)

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

use   select 1 3 0   This will do the job

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

Examples related to sql-server

Examples related to sql-server-2008

Examples related to division

Examples related to rounding