CODEWITHSUNDEEP
sqlsql-servert-sql
fishhead
fishhead

Reputation: 6109

sql rounding value

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

Upvotes: 2

Views: 1074

Answers (2)

Andomar
Andomar

Reputation: 238296

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.

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332731

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.

Upvotes: 2

Related Questions