SQL Round function not working, any ideas?

Question

Here is the SELECT statement:

SELECT ROUND(ISNULL(SUM(Price),0),2) As TotalPrice
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)

Any ideas why this is not rounding to two decimal places?

Answer 1

instead ROUND(ISNULL(SUM(Price),0),2)you can tryCAST(ISNULL(SUM(PRICE),0) AS DECIMAL (4,2))

Answer 2

You might have sorting problems for a column in your environment. You can try explicit casting of CAST (ROUND (...) AS NUMERIC (18,4)) or even try making 0 0.0. Make sure that you also bind the column to the appropriate data type in your application.

All cool people use COALESCE instead of ISNULL. COALESCE is portable, and you can have as many options as you want (not just two!)

, , DA , .

Answer 3

What type of data is the price?

ROUND in BOL

SELECT ROUND(123.4545, 2); -- = 123.4500
GO
SELECT ROUND(123.45, -2);  -- = 100,00
GO

The basic data type remains the same: you just round, but leave trailing zeros.

To output 2 decimal places you need CAST to decimal (x, 2)