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 of why it's not rounding to two decimal places?

Solution 1

instead of ROUND(ISNULL(SUM(Price),0),2) you could try CAST(ISNULL(SUM(PRICE),0) AS DECIMAL (4,2))

Solution 2

What datatype is Price?

ROUND in BOL

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

The underlying datatype stays the same: you merely round but leave trailing zeros.

For 2 decimal place output, you'd need to CAST to decimal(x, 2)

Solution 3

You might be having marshalling issues for the column in your environment. Might try an explicit cast CAST(ROUND(...) AS NUMERIC(18,4)) or even just try making 0 0.0. Make sure also you are binding the column with the proper datatype in your application.

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

Anyway I'm not sure if this was just an example but you may also have DA issues with your data if it had not already been rounded at this stage.

Comments

[Einstein]

ISNULL can generate better execution plans if used in the WHERE clause. It has no tangable effect on performance of result sets.

[ZygD]

So cool people are inconsistent? ISNULL in WHERE clauses, COALECSE in SELECT clauses...

[Tony Morello]

As stated, COALESCE is portable... ISNULL -> ISNOT :P

[Jacob Hulse]

Had a similar issue. Converting the FLOAT datatypes to DECIMAL worked for me