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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

select cast  height as decimal  10 as HeightDecimal

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

Examples related to sql

Examples related to sql-server

Examples related to type-conversion