Truncate (not round) decimal places in SQL Server

203

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 automatically round @value to be 123.46, which is good in most cases. 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. Are there any other ways?

This question is tagged with sql sql-server tsql rounding

~ Asked on 2008-09-04 15:50:41

20 Answers


200

select round(123.456, 2, 1)

~ Answered on 2008-09-04 15:54:24


285

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.

~ Answered on 2008-09-04 16:11:32


39

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

~ Answered on 2009-05-05 19:58:28


13

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

~ Answered on 2013-12-31 13:40:16


13

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

~ Answered on 2009-02-12 22:54:22


8

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)

~ Answered on 2008-09-04 15:57:42


7

Round has an optional parameter

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

~ Answered on 2009-11-12 22:33:24


6

Another truncate with no rounding solution and example.

    Convert 71.950005666 to a single decimal place number (71.9)
    1) 71.950005666 * 10.0 = 719.50005666
    2) Floor(719.50005666) = 719.0
    3) 719.0 / 10.0 = 71.9

    select Floor(71.950005666 * 10.0) / 10.0

~ Answered on 2009-02-12 23:03:33


4

This will remove the decimal part of any number

SELECT ROUND(@val,0,1)

~ Answered on 2009-12-11 11:11:58


3

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

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

~ Answered on 2019-09-15 09:48:50


1

Try like this:

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

~ Answered on 2020-10-03 11:15:01


1

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.

~ Answered on 2016-04-13 18:44:12


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?

~ Answered on 2017-01-26 19:24:59


0

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

~ Answered on 2009-07-01 07:09:37


0

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)

~ Answered on 2009-11-20 11:16:17


0

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)

~ Answered on 2018-03-27 07:52:26


0

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

SELECT ROUND(235.415, 2, 1)

will give you 235.410

SELECT ROUND(235.415, 0, 1)

will give you 235.000

But now trimming0 you can use cast

SELECT CAST(ROUND(235.415, 0, 1) AS INT)

will give you 235

~ Answered on 2020-12-11 16:49:51


0

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.

~ Answered on 2019-09-02 15:42:25


-3

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

~ Answered on 2009-10-21 22:02:01


-5

select convert(int,@value)

~ Answered on 2008-09-04 15:52:24


Most Viewed Questions: