In SQL Server 2008, I need to decrease decimal part

Question

In SQL Server 2008, I need to decrease decimal part

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I have the following query:

select (select (select(select sum(paquetes) from koc4 where Material = '1000230')*((select AVG (convert(decimal (10,2),(Prom_lote)))as prom_lot 
from QM where Material = '1000230')/1000) )-((select (select sum(paquetes) from koc4 where Material = '1000230')
*((select convert (decimal (10,2),Peso_bruto) from Maestro where Material = '1000230')/1000) )))
/((select (select sum(paquetes) from koc4 where Material = '1000230')*((select [Peso neto]
 from Maestro where Material = '1000230')/1000)))*100 as percentaje

I get the following result:

percentaje
1.64220000000002

I would like you to only show 1.64

sql sql-server

asked by miguel 21.11.2017 в 13:05

source

2 answers

1

As I mentioned @fedorqui , using ROUND is possible. Now, to get it to reduce (or truncate) the numbers, the data type must have decimal places.

For example, if we round a decimal without decimal places, this would round it off:

  SELECT ROUND( CAST(1.64220000000002 AS decimal), 2)
  // Imprime 2

Now, if we indicate the positions to the data type, it will reduce the number of decimals and give us the expected results:

select  ROUND(cast(1.6422000000 as decimal(10,2)), 2)
// imprime 1.64

So in your case, be sure to convert percentage to decimal places.

answered by 21.11.2017 в 13:42

Avoid blank lines when reading a .csv with opencsv in Java Android Correct way to send data to a view with jpa spring framework

score 1 · Accepted Answer

There is no need to do ROUND . You only need to do the CAST specifying the number of decimals you want:

select cast(1.6422000000 as decimal(10,2)) -- devuelve 1.64
select cast(1.6472000000 as decimal(10,2)) -- devuelve 1.65

In fact, it does not make much sense to apply the ROUND once we have already reduced the decimals to 2, since it would then be too late to apply the ROUND . If anything, the correct way would have been to apply the ROUND first, and then the CAST .