Rounding Problem

Hi all,
Is there any function that truncates a decimal numeric without rounding, if
i use cast it rounds the figure but can i don't want that i.e i want it to
be as follows:
23.1238 turns to 23.123 and not 23.124 so it keeps only three decimals as
is
Please advise if there is any function or so
Thanks in advance
Suad
Try
Round(23.1238,3,1)

rounding numerics

Hello,

Executing this statement outputs 10.0000000000. I expect it to be 9.999999999.

declare @.test NUMERIC(24,10)

declare @.test2 NUMERIC(24,10)

set @.test2 = 0.0000000000

set @.test = 9.999999999

select @.test * (1 - @.test2)

Changing the type of @.test2 to VARCHAR(12,10) corrects the problem. I don't understand why SQL Server does this rounding?

Thanx,

Wouter

Hi,

You need to change the script like this to avoid an implicit cast to an integer:

declare @.test NUMERIC(24,10)
declare @.test2 NUMERIC(24,10)
set @.test2 = 0.0000000000
set @.test = 9.999999999
select @.test * cast(1 - @.test2 as NUMERIC(24,10))

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

At least part of the answer lies with the fact that the "1" in the statement:

select @.test * (1 - @.test2)

is an integer datatype and must be converted for all of the operations to take place. This can be avoided by performing explicit converting to the numeric(24,10) datatype such as:

select @.test * convert (numeric(24,10), (convert (numeric(24, 10),1) - @.test2)) as converted

-- converted
-- -
-- 9.999999999

Also beware that converting to a float might not be exactly what you want either:

select @.test * convert (float,1) - @.test2 as floater

-- floater
-- --
-- 9.9999999989999999

|||{ Obviously, I am in agreement with Geert. :-) }