Truncate (Not Round) Decimal Places in SQL Server

Truncate (not round) decimal places in SQL Server

select round(123.456, 2, 1)

How to Truncate the Decimal Places without Rounding Up?

using the round function you can try this

select round(4.584406, 1, 1)

the output will be

4.5

the key is the third parameter

ROUND ( numeric_expression , length [ ,function ] )

function

Is the type of operation to perform. function must be tinyint,

smallint, or int. When function is
omitted or has a value of 0 (default),
numeric_expression is rounded. When a
value other than 0 is specified,
numeric_expression is truncated.

SQL get decimal with only 2 places with no round

You could accomplish this via the ROUND() function using the length and precision parameters to truncate your value instead of actually rounding it :

SELECT ROUND(3381.5786, 2, 1)

The second parameter of 2 indicates that the value will be rounded to two decimal places and the third precision parameter will indicate if actual rounding or truncation is performed (non-zero values will truncate instead of round).

Example

You can see an interactive example of this in action here.

SQL Query to truncate to two decimal points?

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";

truncate decimal places in SQL server query not returns exact value

You probably just want to convert to a decimal:

Select convert(decimal(4, 1), 7.10) As Amount

A decimal stores a number exactly -- up to the given number of decimal places. In addition, it changes the type of the result, to contain this information. round() merely changes the value but not the type.

How to truncate decimal in Microsoft SQL Server

You could use round this way

ROUND (110.975 , 2 , 1 )

ROUND ( numeric_expression , length [ ,function ] )

function Is the type of operation to perform. function must be
tinyint, smallint, or int. When function is omitted or has a value of
0 (default), numeric_expression is rounded. When a value other than 0
is specified, numeric_expression is truncated.

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms175003(v=sql.90)?redirectedfrom=MSDN

Truncate the result to to two decimal places and round them up to the next whole percent in SQL Server

You need to combine ceiling and cast together, first casting to 2 decimal places, and then rounding up:

select ceiling(cast(96.001 as decimal(10,2)))
union all 
select ceiling(cast(80.01 as decimal(10,2)))

Results in 96 and 81.

• Online Demo

---

Edit, in regards to the comment by Jeroen Mostert, if you need 96.009 to round down to 96, here's an alternative option using round:

select ceiling(round(96.009, 2, 1))

Truncate in SQL Server rounding values

If you cast price and amount to either numeric or decimal data type as shown below, you should arrive at the expected result:

DECLARE @Tables table 
(
    ProductID int,
    Price float,
    Amount float
);

INSERT @Tables
  (ProductID, Price, Amount)
VALUES
  (100, 50.01, 1),
  (101, 25, 0.789);

SELECT ProductID
      ,Price
      ,Amount
      ,ROUND(SUM((CAST(Price AS decimal(5,2)) * CAST(Amount AS decimal(5,3)))),2,1) AS 'Total'
FROM @Tables
GROUP BY ProductID, Price, Amount;

(2 row(s) affected)
ProductID   Price                  Amount                 Total
----------- ---------------------- ---------------------- ---------------------------------------
100         50.01                  1                      50.01000
101         25                     0.789                  19.72000

(2 row(s) affected)

---

Related Topics