ROUND Function - SQL Server to Oracle Migration

In SQL Server, the ROUND function rounds the number to the nearest value with the specified precision, or truncates the number to the specified precision.

In Oracle you can use the ROUND and TRUNC functions.

SQL Server:

  -- Round to 1 decimal digit (rounded to lower value as 4 <= 5)
  SELECT ROUND(123.34, 1);
  /* 123.30 */  
 
  -- Round to 1 decimal digit (rounded to upper value as 6 > 5)
  SELECT ROUND(123.36, 1);
  /* 123.40 */  
 
  -- When the 3rd parameter is non-zero then the value is just truncated
  SELECT ROUND(123.36, 1, 1);
  /* 123.30 */

Oracle:

  -- Round to 1 decimal digit (rounded to lower value as 4 <= 5)
  SELECT ROUND(123.34, 1) FROM dual;
  /* 123.30 */  
 
  -- Round to 1 decimal digit (rounded to upper value as 6 > 5)
  SELECT ROUND(123.36, 1) FROM dual;
  /* 123.40 */  
 
  -- TRUNC function has to be used for truncation
  SELECT TRUNC(123.36, 1) FROM dual;
  /* 123.30 */

Oracle does not support ROUND with 3 parameters, so you need to use TRUNC for truncation.

Negative Precision to Round Integer Part

If the precision is negative, the ROUND function rounds or truncates the integer part:

SQL Server:

  -- Round to tens (rounded to lower value as 3 <= 5)
  SELECT ROUND(123, -1);
  /* 120 */  
 
  -- Round to hundreds (rounded to upper value as 56 > 50)
  SELECT ROUND(156, -2);
  /* 200 */  
 
  -- When the 3rd parameter is non-zero then the value is just truncated
  SELECT ROUND(156, -2, 1);
  /* 100 */

Oracle:

  -- Round to tens (rounded to lower value as 3 <= 5)
  SELECT ROUND(123, -1) FROM dual;
  /* 120 */  
 
  -- Round to hundreds (rounded to upper value as 56 > 50)
  SELECT ROUND(156, -2) FROM dual;
  /* 200 */  
 
  -- TRUNC function has to be used for truncation
  SELECT TRUNC(156, -2) FROM dual;
  /* 100 */

For more information, see SQL Server to Oracle Migration.