Prevent Rounding when using Format Masking [message #671125] |
Thu, 16 August 2018 00:04 |
|
abhi_orcl
Messages: 40 Registered: December 2016
|
Member |
|
|
Hello All,
I have a procedure in which I am doing come calculations. Now, the results return 9.990991768765.
I need to format this to with precision and scale as 18,3. Also, as per the requirement, need to add 0 after the decimals to always show 3 places after decimal. So I use, to_char('9.990991768765','FM999999999999999.000').
But, Oracle always rounds off and fives me 9.991 instead of the desired 9.990
Is there a way I can stop the rounding.
Thanks
|
|
|
|
Re: Prevent Rounding when using Format Masking [message #671136 is a reply to message #671125] |
Thu, 16 August 2018 06:46 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
abhi_orcl wrote on Thu, 16 August 2018 00:04Hello All,
I have a procedure in which I am doing come calculations. Now, the results return 9.990991768765.
I need to format this to with precision and scale as 18,3. Also, as per the requirement, need to add 0 after the decimals to always show 3 places after decimal. So I use, to_char('9.990991768765','FM999999999999999.000').
But, Oracle always rounds off and fives me 9.991 instead of the desired 9.990
Is there a way I can stop the rounding.
Thanks
Aside from your question, your use of to_char is flawed. to_char accepts either a number or a date, but you are passing it a character string, forcing oracle to do an implicit conversion of the string to a number (to_num) before passing it to to_char. I know that what you are passing looks like a number to you, but you enclosed it in single quotes. That makes it a character string, even if all of the characters are numeric.
Actually, that could contribute to your problem, because the implicit conversion of '9.990991768765' to a number may not be happening the way you think. In any event, your to_char should read:
to_char(9.990991768765,'FM999999999999999.000') - that's without the single quotes around the number.
|
|
|
Re: Prevent Rounding when using Format Masking [message #671190 is a reply to message #671136] |
Thu, 16 August 2018 11:32 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
The correct value when you round the number 9.990991768765 to 3 significant digits is 9.991. However if all you want to do is use the 3 number to the right of the decimal place use the following
select to_char(floor(9.990991768765* 100)/100,'FM999999999999999.000') from dual
|
|
|
|
|
|