Search code examples
sqlsql-serverinteger-division

SQL Query Mathematic operation

I Exec below code in SQL Server Management Studio

SELECT       CAST (9/100 AS DECIMAL(8,3))

this should return 0.090 but Return 0.000 .

Who can tell what the problem is and why it gives me this output?

Solution

  • The problem with your current approach is that the 9/100 ratio is computed using integer division before you make the cast to decimal. By the time that cast happens, it is already too late and you've lost the decimal component. Simply I would just do:

    SELECT 9 / 100.0;  -- 0.090000

    So long as the numerator or denominator be a decimal, the quotient will also carry the decimal result.