How to truncate the decimal values in sql server 2000

Question

I have a expression : Select (2345789 * 39.456)/100

The output is 925554.5078400.

I would like to display 925554.50 i.e. 2 values after decimal.

If I use round ( Select round((2345789 * 39.456)/100 ,1) ), the outout is

925554.5000000. But I want exactly 2 digits after the decimal.

How to do that?

Answer 1

Try (not tested):

SELECT CAST(number as decimal(18,2))

Answer 2

Stating the obvious, but 925554.5000000 and 925554.50 are the same number.

If you want to display that number with two decimal places then that is an issue for your UI code, not your database.

Having said that, if you must do this in the database itself then try something like:

-- 1 decimal place
SELECT CAST(ROUND(@yourNumber, 1, 1) AS DECIMAL(18, 1))

-- 2 decimal places
SELECT CAST(ROUND(@yourNumber, 2, 1) AS DECIMAL(18, 2))

-- 3 decimal places
SELECT CAST(ROUND(@yourNumber, 3, 1) AS DECIMAL(18, 3))

-- 4 decimal places
SELECT CAST(ROUND(@yourNumber, 4, 1) AS DECIMAL(18, 4))

Answer 3

If the rounding is done correctly the answer should be 925554.51.

e.g.

SELECT CAST(((2345789 * 39.456)/100) AS DECIMAL(18,2))