# Thread: How to specify precision of number in SQL

1. Registered User
Join Date
Dec 2002
Posts
123

## Unanswered: How to specify precision of number in SQL

Hi,

I have the value +3.84000020000000E+001 and would like it to be displayed as 3.84 so basically I would like to round it off to the 2nd decimal place and specify precision for the number of digits to display.. I found the ROUND function but can't find anything to specify the precision.. any thoughts? Thanks!

2. Registered User
Join Date
May 2009
Posts
509
db2user, the ROUND function has two parameters. The first is the number you want to round and the second is the number of places to the right of the decimal point (the precision).

Ex. ROUND(3.8451, 2) would return 3.85

PS I am not sure if +3.84000020000000E+001 would be returned as a Decimal. You may need to use CAST to convert it if you want a different datatype.

3. Registered User
Join Date
Dec 2002
Posts
123
thanks for the reply -- this is what I get if I use the ROUND function --

doesn't seem to remove the scientific notation

4. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Code:
```select decimal(+3.84000020000000E+001, 31, 2)
from sysibm.sysdummy1```
Result:

38.40
Lenny

5. Registered User
Join Date
Dec 2002
Posts
123
thanks, you're quite a db2 pro!

6. Registered User
Join Date
May 2009
Posts
509
db2user, That is what the PS was for. I am not at a place where I could test to be sure so I tried to indicate you might need to convert it buy using either the CAST or DECIMAL function (as Lenny showed).

By the way, Lenny77, will that Round or Truncate to 2 decimal places. What if the value was +3.84050020000000E+001? Would you get 38.4 or 38.5?

7. Registered User
Join Date
Dec 2002
Posts
123
thanks Stealth_DBA.. I just wasn't sure how to use the decimal function.. but you ask a good question since i want the number to be rounded and then cut off..

8. Registered User
Join Date
Dec 2002
Posts
123
I think this should do it in any case --

It turns out that this works just fine when I run it from the command line.. but I really want this value returned as a part of a stored procedure. I have an OUT parameter READINGVAL which is of DOUBLE type and decimal(ROUND(+3.84000020000000E+001,2), 31, 2) is assigned to it and returned.

When I run the procedure, I still get it in the long scientific format --
Parameter Value : +3.84000000000000E+001

Any way around this? Should I return it as a string instead?

I tried it with the output parameter as DECIMAL type where I'm doing something like --

SET READINGVAL = DECIMAL(ROUND(+3.84000020000000E+001,2), 31, 2); and it returns "38." instead of "38.40"

Thanks!
Last edited by db2user; 10-19-09 at 13:58.

9. Registered User
Join Date
Dec 2002
Posts
123
I figured it out.. by setting the output parameter as DECIMAL(31,2) instead of DECIMAL..

10. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

## Good job

Originally Posted by db2user
I figured it out.. by setting the output parameter as DECIMAL(31,2) instead of DECIMAL..
Good job for user !

Lenny

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•