Rounding Numeric Value | Database

Introduction

A numeric value can be rounded by built in functions

ROUND()
FLOOR()
CEILING()

ROUND

ROUND() function rounds a number to a specified number of decimal places. Syntax for the ROUND function is

ROUND (number, [decimal_place])

decimal_place indicates the number of decimal points returned. A negative number means the rounding will occur to a digit to the left of the decimal point. For example, -1 means the number will be rounded to the nearest tens.

SELECT ROUND(31.415926, -1) AS RoundValue
SELECT ROUND(31.415926) AS RoundValue
SELECT ROUND(31.415926, 1) AS RoundValue

/* Result */
30
31
31.4

FLOOR

FLOOR() function returns the largest integer value that is smaller than or equal to a number. Syntax of FLOOR() function is

FLOOR(number)

Following example shows floor of positive and negative number.

SELECT FLOOR(31.415926) AS FloorValue
SELECT FLOOR(-31.415926) AS FloorValue

/* Result */
31
-32

CEILING

CEILING() function returns the smallest integer value that is larger than or equal to a number. Syntax of CEILING() function is

CEILING(number)

Below example shows ceiling of positive and negative number.

SELECT CEILING(31.415926) AS CeilValue
SELECT CEILING(-31.415926) AS CeilValue

/* Result */
32
-31

Reference

CEILING() Function