How to convert integer to decimal in SQL Server query?

Question

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.

Answer 1

SELECT height/10.0 AS HeightDecimal FROM dbo.whatever; 

If you want a specific precision scale, then say so:

SELECT CONVERT(DECIMAL(16,4), height/10.0) AS HeightDecimal   FROM dbo.whatever; 

Answer 2

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)