Truncate (not round) decimal places in SQL Server

Question

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?

Solution 1

select round(123.456, 2, 1)

Solution 2

ROUND ( 123.456 , 2 , 1 )

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

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.

Solution 3

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

Solution 4

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

Solution 5

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

View more solutions

Comments

[Ryan Eastabrook]

Sorry if I was not clear, I need to keep the decimal places, just drop the ones that I don't want. For example, instead of 123.456 in my example above being converted to 123.46...I want to drop the third decimal and make it 123.45.

[deroby]

+1 for Floor() which is the way to go to drop decimals without rounding indeed. Too bad Floor() has no second parameter to indicate at which position. But I think the * 10) / 10 works around this quite well indeed.

[John]

This isn't what the OP wanted. He still wants decimal places, but he wants to remove them (truncte) rather than rounding them. I.E. 123.456 -> 123.45 NOT 123.456 -> 12.46 and NOT 123.456 -> 123

[Dave]

Anybody know what values for the function argument correspond to tinyint, smallint and int? Microsoft left that part out of their documentation and can't find the answer anywhere. msdn.microsoft.com/en-us/library/ms175003(SQL.90).aspx

[Warren LaFrance]

MS SQL SERVER rounds numbers a bit differently than IQ. To fix use the round(x,y,z) function like this round(val1/val2,4,1)

[Bikram]

Select round(123.456, 2, 1) will = 123.450

[remykarem]

I don't see why this gets upvotes. The above will give you 123.450 and 123.460 respectively.

[remykarem]

It does not. SELECT ROUND(123.4560,0,1) gives you 123.0000 instead.

[Tyler]

The answer should explain what the parameters of the function are

[Midhun Darvin]

SQL ROUND(number, decimals, operation) : operation --> 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 is 0

[Damián Pablo González]

1) 3rd line is completely unnecessary 2) he explicitly said he didn't want to round the number, but truncate it (the result should be 123.45)

[Arulmouzhi]

ODBC Scalar Function - Different Alternative !

[8128]

I don't understand this answer. The given SQL results in 10.01, which is a correct rounding of 10.0055 to two decimal places. If the final parameter is not 0, the value is truncated (to 2 decimal places) to 10.00.

[Spider]

you are a life saver, i had a amount to show last two digits only "1.9991666", however it always rounded up to 2 no matter what I use, with this I am able to achieve something like this - select FORMAT(round(1.9991666, 2, 1),'N2') AS 'Rate', Thanks mate

[Mufaddal]

For this eg: it does not return the value : 123.45, rather it returns the value 123.450. Please advice and help. I am using SQL 14

[mandy1339]

I do not believe this is correct Consider the following case and notice that the result is rounded up instead of Truncated. OP wants it truncated. ` DECLARE @N1 AS DECIMAL(10,7) = 123.1234789 ` PRINT @N1 ` SELECT CAST(@N1 AS DECIMAL(10,4))