• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Tag Archives: ROUNDING

Rounding monetary values

26 Friday Feb 2010

Posted by troroblog in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

Tags

ORACLE, ROUNDING, SQL

A question that occurs from time to time is how to round a (monetary) amount to e.g. 5, 25, 50 cents. Basically this can be done easily using the ROUND function.

ROUNDING to 5 cents = ROUND(amount*20)/20
amount   multiplied by 20   rounded to integer   divided by 20
------   ----------------   ------------------   -------------
  1.02              20.40                   20            1.00 
  1.03              20.60                   21            1.05 
  1.37              27.40                   27            1.35

the same principle works for:

  •  round to 25 cents = ROUND(amount*4)/4
  •  round to 50 cents = ROUND(amount*2)/2

basically the general formula is:

ROUND(amount*(100/rounding_base)) / (100/rounding_base)

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...