CODEWITHSUNDEEP
sqloracle-databaserounding
Manual
Manual

Reputation: 1687

Rounding to 2 decimal places in SQL

every time I want to round to 2 decimal places when it comes to zeros it doesnt want to round it... how could I round it to 2 decimal places with zeros at the end which would give me 92.00 instead of just 92 ???

SELECT ROUND(COLUMN_NAME,2) FROM ....

it's giving me 

COLUMN_NAME
92

but I want 

COLUMN_NAME
92.00

I used TO_CHAR and it worked

ROUND(TO_CHAR(COLUMN_NAME),2)

thanks guys!

Upvotes: 26

Views: 195792

Answers (5)

Imran
Imran

Reputation: 169

Try this...

SELECT TO_CHAR(column_name,'99G999D99MI')
as format_column
FROM DUAL;

Upvotes: 0

newbie
newbie

Reputation: 155

This works too. The below statement rounds to two decimal places.

SELECT ROUND(92.258,2) from dual;

Upvotes: 14

alzaimar
alzaimar

Reputation: 4622

Try to avoid formatting in your query. You should return your data in a raw format and let the receiving application (e.g. a reporting service or end user app) do the formatting, i.e. rounding and so on.

Formatting the data in the server makes it harder (or even impossible) for you to further process your data. You usually want export the table or do some aggregation as well, like sum, average etc. As the numbers arrive as strings (varchar), there is usually no easy way to further process them. Some report designers will even refuse to offer the option to aggregate these 'numbers'.

Also, the end user will see the country specific formatting of the server instead of his own PC.

Also, consider rounding problems. If you round the values in the server and then still do some calculations (supposing the client is able to revert the number-strings back to a number), you will end up getting wrong results.

Upvotes: 8

Rohan
Rohan

Reputation: 2030

you may try the TO_CHAR function to convert the result

e.g.

SELECT TO_CHAR(92, '99.99') AS RES FROM DUAL

SELECT TO_CHAR(92.258, '99.99') AS RES FROM DUAL

Hope it helps

Upvotes: 34

Aaron
Aaron

Reputation: 57738

Try using the COLUMN command with the FORMAT option for that:

COLUMN COLUMN_NAME FORMAT 99.99
SELECT COLUMN_NAME FROM ....

Upvotes: 5

Related Questions