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

select convert(int,@value)

Answer 2

select round(123.456, 2, 1)

Answer 3

@SQLMenace:

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.

Answer 4

Do you want the decimal or not?

if not use

select ceiling(@value),floor(@value)

if you do with 0 the do a round

select round(@value,2)

Answer 5

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

Answer 6

If you wanted to drop one of the decimal places from a number with three values after the decimal point, you could multiply your value by 100, cast it to an int, cast back to a decimal and divide by 100. For example:

declare @val decimal (18, 3)
select @val = 123.456
select @val = cast(cast(@val * 100 as int) as decimal (18, 3)) / 100

select @val

This will output "123.450", so you'd need a second value declared as a decimal (18, 2) to hold the final number sans trailing 0. However, considering how easy it would be to just cast to a string and use left(), I'm not sure this is worth using.

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

Round has an optional parameter

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

Answer 13

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)

Answer 14

This will remove the decimal part of any number

SELECT ROUND(@val,0,1)