CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Eric Klaus
Eric Klaus

Reputation: 955

rounding and converting value

I have some value with data type Numeric(28,10) (e.g. 128000,0000000000). I want to round it up to 2 significances and convert it into string. What is wrong with this?

convert(varchar,round(isnull(td2.Qty,0),2))

where td2.Qty is that value. It coverts it to string, but doesn't round it. Thanks in advance

Upvotes: 0

Views: 68

Answers (3)

Pradeep atkari
Pradeep atkari

Reputation: 569

Let me explain the use of ROUND() function in SQL. It will not return the decimal value for the given length. E.g. round(isnull(td2.Qty,0),2) will not return friction value length 2, but rather

(e.g. 128000.0000000000) to (e.g. 128000.00)

instead of

(e.g. 128000.12340000000) to (e.g. 128000.120000000000).

It returns only after decimal 2 value and remain all '0'. In your case you want ROUND to truncate. so you can use

Cast(round(isnull(td2.Qty,0),2)as decimal(18,2));

Upvotes: 0

mohan111
mohan111

Reputation: 8865

SELECT CAST(round(isnull(128000,0000000000),2)AS FLOAT)
SELECT CAST(round(isnull(128000,0000000000),2)AS NUMERIC(28,2))

Upvotes: 0

GSerg
GSerg

Reputation: 78175

It does round, but it keeps displaying the zeros because this is how numerics are always displayed.

If you need to stop displaying zeros, convert the value to a different type after the rounding, e.g. float or numeric(28,2):

convert(varchar, cast(round(isnull(td2.Qty,0),2) as numeric(28,2)))

Upvotes: 1

Related Questions