Geek Answers Handbook

Removing trailing zeros with sql

Possible duplicate:
Remove trailing zeros from decimal in SQL Server

I am trying to use a round function and not show any of the trailing zeros, but still getting some zeros at the end. I should get 10.4, but I get something like this:

10.400000

Here is my sql:

 select round(AVG(CAST(k.TotalNumberDays AS numeric(12,2))),2) TotalNumber

How can I remove trailing zeros here? I need to show only 10.4.

+6
source share
4 answers

You just need to specify it as a decimal (12,2).

 select cast(round(AVG(CAST(k.TotalNumberDays AS numeric(12,2))),2) as decimal(12,2)) TotalNumber
+8
source

SQL Server supports floating point data type formats without trailing zeros, so you can remove trailing zeros by converting them to float. For instance:

Select Cast (10,40000 as floating)

This returns 10.4

However, this is a presentation problem and should really be done in your presentation layer, and not waste resources on different types of data.

+7
source

If I try this:

 SELECT(ROUND(CAST(10.4 AS numeric(12,2)), 2)

I get:

 10.40

If you use numeric or decimal types, you will get as many zeros as you set in the precision part of the data type.

In your example, you specified 2 digits of precision, so you will always have up to two trailing zeros. Perhaps you need a different data type, like float .

+1
source

Just move your CAST from avg, like here:

 select CAST(round(AVG(10.3543435),2) as numeric(12,1)) TotalNumber --------------------------------------- 10.4 (1 row(s) affected)
+1
source

More articles:


All Articles