2026-09-11 · 8 min read
- sql
- decimal
- money
Exact financial calculations require more than choosing a DECIMAL column. The application parser, database driver, expression type rules, storage precision, aggregate behavior, serialization, and display layer can each change a value or apply a different rounding mode.
Binary floating-point is useful for many scientific and graphical calculations, but values such as 0.1 do not have an exact finite binary representation. Converting money through a floating-point type can introduce differences before the database receives it.
Start from the domain's unit and range
For every amount, specify the currency or unit, maximum magnitude, required fractional digits, whether additional calculation precision is retained, and which values are legal. Precision is the total number of significant decimal digits a SQL type stores; scale is the number to the right of its decimal point.
- Use an exact decimal type or integer minor units under a documented contract.
- Do not assume every currency uses two fractional digits.
- Define negative, zero, tax, discount, and refund rules.
- Plan schema changes before amounts approach declared precision.
Control expression and binding types
Bind exact values through the driver's decimal facility or canonical decimal strings rather than first converting them to a binary float. Review how multiplication, division, SUM, AVG, CASE, UNION, literals, and mixed integer or floating expressions derive result precision and scale in the selected database.
Make consequential casts explicit and inspect what the ORM generates. A DECIMAL column does not keep an expression exact if another operand or client conversion already selected an approximate type.
Choose business rounding points once
State whether rounding happens per unit, line item, tax component, invoice subtotal, currency conversion, or settlement. Select a named tie-breaking rule and use compatible maintained decimal implementations in every layer that must reproduce the result.
- Avoid rounding for display and then reusing the display value for calculation.
- Avoid double rounding through an intermediate scale.
- Retain source, rate, result, and rounding policy where audit rules require it.
- Reconcile aggregate totals against the defined line-level policy.
Prove exact round trips
Use Flashman's SQL formatter to inspect casts and expressions, JSON formatter for public decimal-string fixtures, diff for expected calculations, number-base converter to demonstrate binary representation limits, and units converter to document scale. Never paste customer or account data.
Test values below, at, and above half increments; positive and negative ties; smallest and largest allowed values; excess scale; multiplication and division; rates; SUM and AVG; imports and exports; ORM reads and writes; every driver; replicas; schema migrations; locale-independent rendering; and JSON clients that might coerce strings back to numbers.