Home » SQL & PL/SQL » SQL & PL/SQL » Prevent Rounding when using Format Masking (Oracle 12.1.0.2.0, RHEL)
Prevent Rounding when using Format Masking [message #671125] Thu, 16 August 2018 00:04 Go to next message
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 #671126 is a reply to message #671125] Thu, 16 August 2018 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

TRUNC

Re: Prevent Rounding when using Format Masking [message #671136 is a reply to message #671125] Thu, 16 August 2018 06:46 Go to previous messageGo to next message
EdStevens
Messages: 1141
Registered: September 2013
Senior Member
abhi_orcl wrote on Thu, 16 August 2018 00:04
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
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 Go to previous messageGo to next message
Bill B
Messages: 1909
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
Re: Prevent Rounding when using Format Masking [message #671191 is a reply to message #671190] Thu, 16 August 2018 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

TRUNC very well does it:
SQL> select to_char(trunc(9.990991768765,3),'FM999999999999999.000') from dual;
TO_CHAR(TRUNC(9.9909
--------------------
9.990
Re: Prevent Rounding when using Format Masking [message #671197 is a reply to message #671191] Thu, 16 August 2018 12:17 Go to previous messageGo to next message
Bill B
Messages: 1909
Registered: December 2004
Senior Member
This is what I love about Oracle. You learn something new everyday. I never knew that trunc had a significant digits option like round. Your never too old to learn. lol

Thanks Michel
Re: Prevent Rounding when using Format Masking [message #671239 is a reply to message #671190] Fri, 17 August 2018 18:37 Go to previous message
Solomon Yakobson
Messages: 2887
Registered: January 2010
Location: Connecticut, USA
Senior Member
Careful with FLOOR - you might get unexpected result with negative numbers:

select to_char(floor(-9.990991768765* 100)/100,'FM999999999999999.000') from dual
/

TO_CHAR(FLOOR(-9.990
--------------------
-10.000

SQL>

SY.
Previous Topic: Extracting just text from a Blob Column
Next Topic: Row Movement disabled in Partitioned table
Goto Forum:
  


Current Time: Tue Nov 19 10:11:21 CST 2019