SQL query to truncate to two decimal places?

Question

I have a fairly large query at the moment, and I am missing one element ... I need to cut the data to two decimal points (without rounding). I'm Googled, but it's hard for me to adapt it to the current request.

SELECT FechaDeSistema, Code, CASE WHEN DR_Socio = @SocioNum THEN Cantidad END as Debit, CASE WHEN CR_Socio = @SocioNum THEN Cantidad END AS Credit, CASE WHEN DR_Socio = @SocioNum THEN BalanceDebito WHEN CR_Socio = @SocioNum THEN BalanceCredito END AS Balance FROM Ledger WHERE (Debito_Cuenta = @Acct) OR (Credito_Cuenta = @Ncct) ORDER BY FechaDeSistema DESC 

I basically need to reduce the “business” with credit and debit (is that what you say?) To two decimal places. How can I take it off? Thanks in advance!

Answer 1

Just drop it to decimal decimal precision, it will break off the final digits.

 cast(yourColumnName as decimal(19,2)) as desiredColumnName 

In particular, your request code

 SELECT FechaDeSistema, Code, CASE WHEN DR_Socio = @SocioNum THEN cast(Cantidad as decimal(19,2)) END as Debit, CASE WHEN CR_Socio = @SocioNum THEN cast(Cantidad as decimal(19,2)) END AS Credit, CASE WHEN DR_Socio = @SocioNum THEN cast(BalanceDebito as decimal(19,2)) WHEN CR_Socio = @SocioNum THEN cast(BalanceCredito as decimal(19,2)) END AS Balance FROM Ledger WHERE (Debito_Cuenta = @Acct) OR (Credito_Cuenta = @Ncct) ORDER BY FechaDeSistema DESC"; 

Answer 2

CASTing will be round. If you really want to truncate, you can use ROUND with the truncate option like this.

 DECLARE @MyNum DECIMAL(19,4) = 100.1294 SELECT @MyNum 'MyNum' , CAST(@MyNum AS DECIMAL(19,2)) 'Cast will round' , ROUND(@MyNum,2,4) 'Round with truncate option' , CAST(ROUND(@MyNum,2,4) AS DECIMAL(19,2)) 'Better Truncate'