CODEWITHSUNDEEP
sqloracle-databasenumber-formatting
Arah
Arah

Reputation: 33

Adding decimal point and zeroes in Oracle

Is there a way to specify that a double datatype will always have 2 points after the decimal point like currency usually has (oracle db) ?

that is:

100 will be converted to 100.00
101.1 will be converted to 101.10

Tried casting it and setting precision-scale but no luck with that it doesnt add default decimal point and zeroes

SELECT CAST((600.2) AS NUMERIC(28,2)) FROM DUAL;

Gives me 600.2

Upvotes: 3

Views: 13180

Answers (2)

Sefer K
Sefer K

Reputation: 514

SELECT to_char(101, '999.90') FROM DUAL;
SELECT to_char(101.1, '999.90') FROM DUAL;

Upvotes: 3

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

It is just formatting the output in your client.

For example, in SQL*Plus set numformat:

SQL> set numformat 999.99
SQL> SELECT CAST((600.2) AS NUMERIC(28,2)) FROM DUAL;

CAST((600.2)ASNUMERIC(28,2))
----------------------------
                      600.20

You could also use TO_CHAR, but use it only to display, for any number arithmetic you should leave the number as it is.

SQL> select to_char(600.2, '000.00') from dual;

TO_CHAR
-------
 600.20

Upvotes: 2

Related Questions