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

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

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 4

This will remove the decimal part of any number

SELECT ROUND(@val,0,1)

Answer 5

select round(123.456, 2, 1)

Answer 6

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?