[sql] 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!

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.

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

SELECT ROUND(92.258,2) from dual;

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

COLUMN COLUMN_NAME FORMAT 99.99
SELECT COLUMN_NAME FROM ....

Try this...

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