sql rounding value

Question

I have been successful at rounding my value to one decimal point, but I would like to trim off the multitude of trailing zeros. For example, this:

ROUND(SUM(Duration),1)

...ends up giving me:

16.9000000000000000

how do I trim all those trailing zeros.

mssql

Answer 1

Use:

CONVERT(Decimal(9, 1), ROUND(SUM(duration), 1))

The second parameter of the DECIMAL data type is the precision - 1 will give you a single digit after the decimal point.

Answer 2

The round function rounds the number, but it leaves the data type unchanged:

ROUND(3.14,1) --> 3.10

By casting a number to numeric(x,1), you both round it and change it's data type to single digit precision:

CAST(3.14 as numeric(6,1)) --> 3.1

For your query, such a cast could look like:

select cast(sum(duration) as numeric(6,1))

But the eventual display format depends on the client. SQL Server Management Studio will display numeric(x,1) with one digit behind the dot, but it is free to display it in another way. Formatting a number is best done client side, outside of SQL.