Remove trailing zeroes using sql [duplicate]

Question

Possible Duplicate:
Remove trailing zeros from decimal in SQL Server

I am trying to use the round function and not show any of the trailing zeroes but still getting some zeroes at the end. I am suppose to get 10.4 but getting 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 the trailing zeros here? I need to show only 10.4.

Answer 1

SQL Server supports formats float data type without trailing zeros, so you can remove trailing zeroes by casting the to a float. For example:

Select Cast(10.40000 as float)

This returns 10.4

However, this is a presentation issue and really should be done in your presentation layer instead of wasting resources casting to different data types.

Answer 2

You just have to cast it as a decimal(12,2).

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

Answer 3

If I try this:

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

I get:

10.40

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

In your example, you have specified 2 digits of precision, so you will always have up to 2 trailing zeros. Perhaps you need a different datatype, such as float.

Answer 4

Just move your CAST out of avg like here:

select CAST(round(AVG(10.3543435),2) as numeric(12,1)) 

TotalNumber
---------------------------------------
10.4

(1 row(s) affected)