# remove Truncate(not round) decimal places in SQL Server

## sql roundup to 2 decimal places (13)

I'm trying to determine the best way to truncate or drop extra decimal places in SQL without rounding. For example:

``````declare @value decimal(18,2)

set @value = 123.456
``````

This will auto round @Value to be 123.46....which in most cases is good. However, for this project I don't need that. Is there a simple way to truncate the decimals I don't need? I know I can use the left() function and convert back to a decimal...any other ways?

`Mod(x,1)` is the easiest way I think.

Round has an optional parameter

``````Select round(123.456, 2, 1)  will = 123.45
Select round(123.456, 2, 0)  will = 123.46
``````

Another way is `ODBC TRUNCATE` function:

``````DECLARE @value DECIMAL(18,3) =123.456;

SELECT @value AS val, {fn TRUNCATE(@value, 2)} AS result
``````

`LiveDemo`

Output:

``````╔═════════╦═════════╗
║   val   ║ result  ║
╠═════════╬═════════╣
║ 123,456 ║ 123,450 ║
╚═════════╩═════════╝
``````

Remark:

I recommend using built-in `ROUND` function with 3rd parameter set to 1.

Actually whatever the third parameter is, 0 or 1 or 2, it will not round your value.

``````CAST(ROUND(10.0055,2,0) AS NUMERIC(10,2))
``````

I know this is pretty late but I don't see it as an answer and have been using this trick for years.

Simply subtract .005 from your value and use Round(@num,2).

``````declare @num decimal(9,5) = 123.456

select round(@num-.005,2)
``````

returns 123.45

It will automatically adjust the rounding to the correct value you are looking for.

By the way, are you recreating the program from the movie Office Space?

Please try to use this code for converting 3 decimal values after a point into 2 decimal places:

``````declare @val decimal (8, 2)
select @val = 123.456
select @val =  @val

select @val
``````

The output is 123.46

Here's the way I was able to truncate and not round:

``````select 100.0019-(100.0019%.001)
``````

returns 100.0010

``````select 123.456-(123.456%.001)
``````

returns 123.450

Now if you want to get rid of the ending zero, simply cast it:

``````select cast((123.456-(123.456%.001)) as decimal (18,2))
``````

returns 123.45

``````SELECT Cast(Round(123.456,2,1) as decimal(18,2))
``````

I know this question is really old but nobody used sub-strings to round. This as advantage the ability to round really long numbers (limit of your string in SQL server which is usually 8000 characters):

``````SUBSTRING('123.456', 1, CHARINDEX('.', '123.456') + 2)
``````

``````ROUND ( 123.456 , 2 , 1 )
``````

When the third parameter != 0 it truncates rather than rounds

http://msdn.microsoft.com/en-us/library/ms175003(SQL.90).aspx

Syntax

ROUND ( numeric_expression , length [ ,function ] )

Arguments

• numeric_expression Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

• length Is the precision to which numeric_expression is to be rounded. length must be an expression of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.

• 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.

I think you want only the decimal value, in this case you can use the following:

``````declare @val decimal (8, 3)
SET @val = 123.456

SELECT @val - ROUND(@val,0,1)
``````

``````select convert(int,@value)
``````

``````SELECT ROUND(@val,0,1) 