Home » SQL & PL/SQL » SQL & PL/SQL » TRUNCATE
TRUNCATE [message #187492] Mon, 14 August 2006 04:18 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have a number of value 12.80,12.345. I have to truncate it off by 2 digit of decimal.So the output should be 12.80,12.34

Select trunc(12.80,2) from dual;
Select trunc(12.34,2) from dual.

But I am getting the output as 12.8 and 12.34. PLease advice.
Re: TRUNCATE [message #187499 is a reply to message #187492] Mon, 14 August 2006 04:26 Go to previous messageGo to next message
hellcat
Messages: 84
Registered: January 2006
Location: Zug, Switzerland
Member
does this help?
select to_char(trunc(12.80,2),'99.99') from dual;
Re: TRUNCATE [message #187500 is a reply to message #187492] Mon, 14 August 2006 04:27 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Actually, 12.80 = 12.8, isn't it? Now, the question is: where do you want to get 12.80 instead of 12.8? For calculation purposes, it doesn't matter - those are the same numbers.

For displaying purposes, use corresponding format mask. In SQL*Plus you could either use number format for the whole session, or use TO_CHAR function and format numbers as you wish. For example:
SQL> set numformat 999,990.00
SQL> select trunc(12.80, 2) from dual;

TRUNC(12.80,2)
--------------
         12.80

SQL> select to_char(12.80, '999,990.00') from dual;

TO_CHAR(12.
-----------
      12.80

SQL>
If it is about Forms Developer or Report Builder, format screen field using the same format.

Note that '999,990.00' could (should?) be written using Group and Decimal separator: '999G990D00' which would then use your NLS settings instead of "hard-coded" , and .
Re: TRUNCATE [message #187513 is a reply to message #187500] Mon, 14 August 2006 05:36 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks for the reply. But I need the output as number only. Use of TO_CHAR converts it into string.
Re: TRUNCATE [message #187517 is a reply to message #187513] Mon, 14 August 2006 05:39 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want it as a number, then you want 12.8, not 12.80.
If you want to display it to the user as 12.80 then you'll need to convert it to a string.
Previous Topic: Running Shell Scripts through Pl/Sql Procedures
Next Topic: Estimating cache size
Goto Forum:
  


Current Time: Thu Dec 08 12:15:04 CST 2016

Total time taken to generate the page: 0.10341 seconds