GET n decimal places without round in SQL Server

Question

I need to select just two digits from decimal values in SQL Server

16.987490   --> 16.98
8.0189918   -->  8.01
34.5566901  --> 34.55

Answer 1

You can Use ROUND

select CAST(ROUND (16.987490  , 2 , 1 ) as NUMERIC(18,2))
select CAST(ROUND (8.0189918  , 2 , 1 ) as NUMERIC(18,2))
select CAST(ROUND (34.5566901  , 2 , 1 ) as NUMERIC(18,2))

OUTPUT

16.98
8.01
34.55

Answer 2

declare @dec decimal(15,5)

set @dec = 16.987490

select cast(@dec as decimal(19,2))

So the solution is to "force" a conversion to a scale of 2.

solution 2 since first doesn't work:

declare @dec decimal(15,5)

set @dec = 8.0189918

select cast(substring(cast(@dec as varchar(50)), 0, CHARINDEX('.', cast(@dec as varchar(50)), 1) + 3) as decimal(19,2))

Answer 3

The easiest way is to subtract 0.005 and use round(), str(), or cast():

select round(val - 0.005, 2)