trunc and round function in sql

sql oracle truncate rounding negative-number
lakshganga picture lakshganga · Jul 3, 2012 · Viewed 18.5k times · Source

Is trunc and round the same with negative arguments?

       SQL> select round(123456.76,-4) from dual;

        ROUND(123456.76,-4)
         -------------------
         120000

         SQL> select trunc(123456.76,-4) from dual;

          TRUNC(123456.76,-4)
          -------------------
         120000

Answer

BertNase picture BertNase · Jul 3, 2012

No, behavior depends on the value of the significant digit (the 3rd digit (the 3) is the significant one in your case, as it is below 5 round and trunc do the same )

try select trunc(125456.76,-4) from dual (result is 120000) vs select round(125456.76,-4) from dual (result is 130000). Now when the significant digit is 5 (or higher) the results of trunc and round differ.

Related questions

ORA delete / truncate

I'm using SQL loader to load my data into database. Before I insert the data I need to remove existing data in the table: options(skip=1,load=250000,errors=0,ROWS=30000,BINDSIZE=10485760) load data infile 'G:1.csv' "str '^_^'" replace into …

Read More
Truncate date to fiscal year

The following database view truncates the date to the fiscal year (April 1st): CREATE OR REPLACE VIEW FISCAL_YEAR_VW AS SELECT CASE WHEN to_number(to_char(SYSDATE, 'MM')) < 4 THEN to_date('1-APR-'||to_char(add_months(…

Read More
How to truncate any table using its synonym in oracle?

How to truncate any table using its synonym in oracle? -- in Server_A Create Table table_a ( col int); -- in server_B CREATE SYNONYM syn_table_a FOR table_a@dblink_server_a; --insert into INSERT INTO syn_…

Read More