[sql-server] 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?

This question is related to sql-server sql-server-2008 division rounding

The answer is


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

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.


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)

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.


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


use

select 1/3.0

This will do the job.


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

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2008

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Get last 30 day records from today date in SQL Server How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904) SQL Server Service not available in service list after installation of SQL Server Management Studio How to delete large data of table in SQL without log?

Examples related to division

Division in Python 2.7. and 3.3 Python3 integer division How to do integer division in javascript (Getting division answer in int not float)? How can I do division with variables in a Linux shell? Python: Remove division decimal How to get a float result by dividing two integer values using T-SQL? Divide a number by 3 without using *, /, +, -, % operators Why does integer division in C# return an integer and not a float? How to check if number is divisible by a certain number? C++ Best way to get integer division and remainder

Examples related to rounding

How to round a numpy array? How to pad a string with leading zeros in Python 3 Python - round up to the nearest ten How to round a Double to the nearest Int in swift? Using Math.round to round to one decimal place? How to round to 2 decimals with Python? Rounding to 2 decimal places in SQL Rounding to two decimal places in Python 2.7? Round a floating-point number down to the nearest integer? Rounding BigDecimal to *always* have two decimal places