Menu

SQLite round(): Decimal Places, Negative Precision and Examples

Updated on

SQLite round(X, Y) rounds a number to Y digits after the decimal point. The second argument is optional: round(X) rounds to zero decimal places. The result is a floating-point number, not formatted text.

Syntax

round(X)
round(X, Y)
  • X: the value to round.
  • Y: optional number of decimal places; the default is 0.
  • Negative Y is treated as 0. Unlike some other SQL dialects, round(123.179, -1) does not round to the nearest ten.
  • If either supplied argument is SQL NULL, the result is NULL.

Round to one or two decimal places

SELECT
    round(123.179, 1) AS one_place,
    round(123.179, 2) AS two_places,
    round(123.179) AS whole_number;

one_place  two_places  whole_number
---------  ----------  ------------
123.2      123.18      123.0

SQLite returns a numeric value. round(123.179) displays as 123.0 because its result is a REAL value.

Halfway values, negative precision and NULL

SELECT
    round(2.5) AS positive_half,
    round(-2.5) AS negative_half,
    round(123.179, -2) AS negative_places,
    round(NULL, 2) AS missing_value;

positive_half  negative_half  negative_places  missing_value
-------------  -------------  ---------------  -------------
3.0            -3.0           123.0            NULL

These halfway examples round away from zero. SQLite uses floating-point arithmetic, so decimal values are not always represented exactly; do not assume that rounding a binary floating-point value guarantees exact decimal financial calculations.

Round values from a table

WITH prices(item, amount) AS (
    VALUES ('A', 12.345), ('B', 9.876), ('C', NULL)
)
SELECT item, round(amount, 2) AS rounded_amount
FROM prices;

item  rounded_amount
----  --------------
A     12.35
B     9.88
C     NULL

This rounds the query result; it does not change the stored column values.

round() versus printf() for display

round() returns a numeric result and does not preserve trailing zeroes for display. Use printf() when you need a string with a fixed number of decimal places:

SELECT
    round(3.5, 2) AS number_value,
    printf('%.2f', 3.5) AS display_text,
    typeof(round(3.5, 2)) AS numeric_type,
    typeof(printf('%.2f', 3.5)) AS text_type;

number_value  display_text  numeric_type  text_type
------------  ------------  ------------  ---------
3.5           3.50          real          text

For different rounding directions, compare trunc(), ceil(), and floor(). See the official SQLite round() definition for the function contract.

Try it in SQLite Playground

Run these queries in the browser-based SQLite Playground. It executes SQL in a temporary local database without requiring a server connection.