9 Replies Latest reply on Feb 15, 2018 3:12 PM by L. Fernigrini

# number with 2 decimal

Hi team,

I want to display Number with 2 decimals (no \$)”, for example, if the value is ‘30’, the output should look like ’30.00’; if the value is ‘42.8’, the output should look like ’42.80’.

I dont want to round the value to nearest value.

I used round(column,2) but if the value if 30 The output is still showing as 30 not 30.00

below is the sample data..

WITH A AS(

SELECT 48.1  amount,30 amount1,234.455 amount2 from dual )

SELECT

CAST(round(amount,2) AS FLOAT)   amount

,CAST(round(amount1,2) AS FLOAT)  amount1

,cast(round(amount2,2) as float)  amount2

from A;

when i executed the result was like below

48.1      30    234.46

But expected is

481.10    30.00     234.46

Thanks

• ###### 1. Re: number with 2 decimal

Just to display?

Try something like:

select to_char(1003.1415926,'999,999,990.00')

from dual

;

Or, if you still want to have it as number, try something like:

select trunc(1003.1415926,2)

from dual

;

• ###### 2. Re: number with 2 decimal

The number is displayed as a string - thus the binary number needs to be converted into a string display format.

The SQL function for explicitly converting the binary number into a display string, is TO_CHAR(). Display formats are well documented with this command, in the SQL Reference Guide.

• ###### 3. Re: number with 2 decimal

Thanks But this goes to char .

Actual data type from table is NUMBER is there any way to do with number or shall we again do the CAST on same ?

• ###### 4. Re: number with 2 decimal

Thanks But this goes to char .

Actual data type from table is NUMBER is there any way to do with number or shall we again do the CAST on same ?

A number is a number regardless.... so 30 is exactly the same number as 30.00.

What you are talking about is a display issue... you want to "display" the value with 2 decimal places.

Read the community document: PL/SQL 101 : DataTypes - NUMBER

as it's such a common question.

• ###### 5. Re: number with 2 decimal

You can use the SET NUMFORMAT configuration on SQL*Plus to achieve that.

• ###### 6. Re: number with 2 decimal

That of course assumes the OP is using SQL*Plus.

It's UI dependent, so different UI's may have different ways of providing a specific format for specific data.

That may be appropriate (as it can then allow the data to be language dependent based on the clients own user settings) but otherwise TO_CHAR would be more appropriate to enforce a particular format.

• ###### 7. Re: number with 2 decimal

Yes, that's assuming OP uses SQL*Plus, but on every case, that should be handled on the presentation layer.

Data format should be handled at presentation layer, if the issue is how to show that number in a specific form/page of OP's application, then that should be done on that application and not from the database. The database should return a NUMBER and then the presentation layer has to "adapt" it's format to the one desired. Usually DB does not know (and does not have to know) of language/geographic/cultural presentation preferences unless is a 2 tier (client-server) environment..

• ###### 8. Re: number with 2 decimal

Unless the database is being the presentation layer i.e. it's the one that is generating a report to a file or suchlike.

• ###### 9. Re: number with 2 decimal

Yes, or generating files to be imported in another system that expects that particular format. There are always exceptions to the rules . I just wanted to provide another way to achieve the problem without changing the datatype of the data, and doing in the best "layer",  the one that is closest to the person.