Truncate (not round) decimal places in SQL Server

Do you want the decimal or not?

If not, use

select ceiling(@value),floor(@value)

If you do it with 0 then do a round:

select round(@value,2)

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

Another way is **`ODBC TRUNCATE`** function:

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

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

<kbd>**[`LiveDemo`](

[To see links please register here]

;

Output:

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


Remark:

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

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?

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)

select round(123.456, 2, 1)

I think we can go much easier with simpler example solution found in Hackerrank:

> Problem statement: Query the greatest value of the Northern Latitudes
> (LAT_N) from STATION that is less than 137.2345. Truncate your answer
> to 4 decimal places.

SELECT TRUNCATE(MAX(LAT_N),4)
FROM STATION
WHERE LAT_N < 137.23453;

Solution Above gives you idea how to simply make value limited to 4 decimal points. If you want to lower or upper the numbers after decimal, just change 4 to whatever you want.

SELECT CAST(Value as Decimal(10,2)) FROM TABLE_NAME;

Would give you 2 values after the decimal point. (MS SQL SERVER)

Try like this:

SELECT cast(round(123.456,2,1) as decimal(18,2))

ROUND(number, decimals, operation)

number => Required. The number to be rounded
decimals => Required. The number of decimal places to round number to
operation => Optional. If 0, it rounds the result to the number of decimal. If another value than 0, it truncates the result to the number of decimals. Default value is 0

```lang-sql
SELECT ROUND(235.415, 2, 1)
```
will give you `235.410`

```lang-sql
SELECT ROUND(235.415, 0, 1)
```
will give you `235.000`


But now trimming`0` you can use `cast`
```lang-sql
SELECT CAST(ROUND(235.415, 0, 1) AS INT)
```
will give you `235`