SQL Query to truncate to two decimal points?

Question

I have a pretty big query at the moment and I am missing one element... I need to cut the data to two decimal points (no rounding needed). I Googled but have had trouble adapting it to the current query.

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 truncate the Credit and Debit "cases" (is that how you say that?) to two decimal points. How can I pull it off? Thanks in advance!

Answer 1

CASTing will 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'

Answer 2

Just cast it to a lower precision decimal, this will chop off the trailing digits.

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

More specifically to your query 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";