# 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?

##### Answer #1

`Mod(x,1)`

is the easiest way I think.

##### Answer #2

Round has an optional parameter

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

##### Answer #3

Another way is ** ODBC TRUNCATE** function:

```
DECLARE @value DECIMAL(18,3) =123.456;
SELECT @value AS val, {fn TRUNCATE(@value, 2)} AS result
```

Output:

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

Remark:

I recommend using built-in `ROUND`

function with 3rd parameter set to 1.

##### Answer #4

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))
```

##### Answer #5

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

Your example:

```
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?

##### Answer #6

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

##### Answer #7

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

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

returns 100.0010

And your example:

```
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

##### Answer #8

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

##### Answer #9

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)
```

##### Answer #10

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

##### Answer #11

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)
```

##### Answer #12

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

##### Answer #13

This will remove the decimal part of any number

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