[sql] How to convert integer to decimal in SQL Server query?

A column height is integer type in my SQL Server table. I want to do a division and have the result as decimal in my query:

Select (height/10) as HeightDecimal

How do I cast so that the HeightDecimal is no longer integer? Thanks.

This question is related to sql sql-server type-conversion

The answer is


You can either cast Height as a decimal:

select cast(@height as decimal(10, 5))/10 as heightdecimal

or you place a decimal point in your value you are dividing by:

declare @height int
set @height = 1023

select @height/10.0 as heightdecimal

see sqlfiddle with an example


declare @xx int 
set     @xx = 3 
select @xx      
select @xx * 2  -- yields another integer  
select @xx/1    -- same
select @xx/1.0  --yields 6 decimal places 
select @xx/1.00 --       6 
select @xx * 1.0  --     1 decimal place - victory
select @xx * 1.00 --     2         places - hooray 

Also _ inserting an int into a temp_table with like decimal(10,3) _ works ok.


select cast (height as decimal)/10 as HeightDecimal

SELECT CAST(height AS DECIMAL(18,0)) / 10

Edit: How this works under the hood?

The result type is the same as the type of both arguments, or, if they are different, it is determined by the data type precedence table. You can therefore cast either argument to something non-integral.

Now DECIMAL(18,0), or you could equivalently write just DECIMAL, is still a kind of integer type, because that default scale of 0 means "no digits to the right of the decimal point". So a cast to it might in different circumstances work well for rounding to integers - the opposite of what we are trying to accomplish.

However, DECIMALs have their own rules for everything. They are generally non-integers, but always exact numerics. The result type of the DECIMAL division that we forced to occur is determined specially to be, in our case, DECIMAL(29,11). The result of the division will therefore be rounded to 11 places which is no concern for division by 10, but the rounding becomes observable when dividing by 3. You can control the amount of rounding by manipulating the scale of the left hand operand. You can also round more, but not less, by placing another ROUND or CAST operation around the whole expression.

Identical mechanics governs the simpler and nicer solution in the accepted answer:

  SELECT height / 10.0

In this case, the type of the divisor is DECIMAL(3,1) and the type of the result is DECIMAL(17,6). Try dividing by 3 and observe the difference in rounding.

If you just hate all this talk of precisions and scales, and just want SQL server to perform all calculations in good old double precision floating point arithmetics from some point on, you can force that, too:

SELECT height / CAST(10 AS FLOAT(53))

or equivalently just

SELECT height / CAST (10 AS FLOAT)

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

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 type-conversion

How can I convert a char to int in Java? pandas dataframe convert column type to string or categorical How to convert an Object {} to an Array [] of key-value pairs in JavaScript convert string to number node.js Ruby: How to convert a string to boolean Convert bytes to int? Convert dataframe column to 1 or 0 for "true"/"false" values and assign to dataframe SQL Server: Error converting data type nvarchar to numeric How do I convert a Python 3 byte-string variable into a regular string? Leading zeros for Int in Swift