Search code examples
sqlitefloating-pointrounding

Rounding to nearest or up in SQLite

I've got an existing calculation (in SQL) which contains several formulas involving rounding. Now I've got to make sure that the rounding uses the half up tie-breaking alorithm.

It appears that by default, SQLite's round function uses the half to even algorithm.

Is there a simple way to change that to half up?

Solution

  • The built-in round function rounds towards negative infinity.

    There is no simply way to do half-to-even directly in SQL.

    You'd have to install your own custom rounding function, or modify the SQLite source code:

    if( n==0 && r>=0 && r<LARGEST_INT64-1 ){
  r = (double)((sqlite_int64)(r+0.5));
}else if( n==0 && r<0 && (-r)<LARGEST_INT64-1 ){
  r = -(double)((sqlite_int64)((-r)+0.5));
}else{
  zBuf = sqlite3_mprintf("%.*f",n,r);
  sqlite3AtoF(zBuf, &r, sqlite3Strlen30(zBuf), SQLITE_UTF8);
}