Truncate (not round) decimal places in SQL Server

sql sql-server tsql rounding
Ryan Eastabrook picture Ryan Eastabrook · Sep 4, 2008 · Viewed 659.2k times · Source

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?

Answer

Jeff Cuscutis picture Jeff Cuscutis · Sep 4, 2008 
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.

Related questions

Round to n Significant Figures in SQL

I would like to be able to round a number to n significant figures in SQL. So: 123.456 rounded to 2sf would give 120 0.00123 rounded to 2sf would give 0.0012 I am aware of the ROUND() function, which rounds to n decimal places …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More