Why does 4 / 5 = 0? in SQL

SQL Server

You may be startled to see the result of this calculation in SQL Server:

SELECT 4 / 5 ----------- 0 (1 row(s) affected)

This is because SQL Server is performing integer division... which ignores decimals / fractions / remainders. There are a couple of ways you can force the behavior you really want:

SELECT 4.0 / 5 SELECT 4 * 1.0 / 5 SELECT CAST(4 AS DECIMAL(5,1)) / 5

Now, you may not like that there are extra decimal places. To handle this, you can use CAST or CONVERT to shift the results to use 2 decimal places:

SELECT CONVERT(DECIMAL(6,2), (4.0 / 5)) SELECT CAST((4 * 1.0 / 5) AS DECIMAL(6,2))

You may also want a leading zero, well this is easy too:

SELECT CONVERT(VARCHAR(6), CONVERT(DECIMAL(6,2), (4.0 / 5))) SELECT CAST(CAST((4 * 1.0 / 5) AS DECIMAL(6,2)) AS VARCHAR(6))

There is no formatpercent operator, like we have in VB / VBA. If you want to add percent signs, you have to do it manually:

SELECT CONVERT(VARCHAR(6), CONVERT(DECIMAL(6,2), (4.0 / 5))) + '%'

Or, handle it at the client application (SQL Server really shouldn't be used for formatting issues).