CODEWITHSUNDEEP
sql-serveroracle-databaset-sqlrounding
Microsoft DN
Microsoft DN

Reputation: 10020

ROUND() givin incorrect result in sql

select round(849299099755.3,2) from dual;

Gives

849299099755.3

How can I get 849299099755.30 here???

Upvotes: 1

Views: 410

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

For Oracle:

The ROUND function returns a NUMBER, and round(849299099755.3,2) indeed is equal to 849299099755.3, so it's working correctly. When you want to display it, however, it will use your session's default number format setting.

To format it with two decimal places, you could use the TO_CHAR function, e.g.:

SELECT TO_CHAR(round(849299099755.3,2),'999999999990.00') FROM DUAL;

Upvotes: 1

John Woo
John Woo

Reputation: 263723

convert the datatype onto DECIMAL

SELECT CONVERT(DECIMAL(15,2), ROUND(849299099755.3,2))

or

SELECT STR(ROUND(849299099755.3,2), 20, 2)

Upvotes: 3

Andrey Gordeev
Andrey Gordeev

Reputation: 32459

For SQL Server:

select convert(numeric(28,2),round(849299099755.3,2)) from dual;

Upvotes: 1

Related Questions