Menu
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?
759
MySQL TRUNCATE() Function The TRUNCATE() function truncates a number to the specified number of decimal places.
To get rid of all decimal places without regard to their value, use the INT() function (short for integer). This function takes one argument, the value, and returns whatever is to the left of the decimal point. To get rid of some decimal places without and rounding, use TRUNC(), which is short of truncate.
Overview of SQL TRUNCATE() function The TRUNCATE() function returns n truncated to d decimal places. If you skip d , then n is truncated to 0 decimal places. If d is a negative number, the function truncates the number n to d digits left to the decimal point. The TRUNCATE() function is supported by MySQL.
Values of float are truncated when they are converted to any integer type. When you convert data types that differ in decimal places, SQL Server will sometimes return a truncated result value, and at other times it will return a rounded value.
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.
307
select round(123.456, 2, 1)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
