SQL CEILING() | CEIL() Function


The SQL CEILING() is a function, and return next whole integer(no fractional digits) value that is equal to or greater than a given input floating number from query result.

The SQL CEILING() function will always goes to the next whole whole integer value.

Ex:

  • Ceiling(4) = result is "4"
  • Ceiling(2.87) = result is "3"
  • Ceiling(6.12) = result is "7"
  • Ceiling(1.9) = result is "2"

The SQL Ceiling() function is supports only numeric column or an numeric field based expression.

It can be used in SELECT statement as well in where clause.


SQL CEILING() Syntax

For SQL SERVER / ORACLE / MY SQL

The basic syntax is for an numeric value or numeric expression:


SELECT CEILING(number);

The below syntax is for a given table numeric column or field from a specific table.


SELECT CEILING(Column_name) FROM table_name;


SQL CEILING() Example - Using Expression Or Formula

The following SQL SELECT statement returns the next whole integer(without fraction digits) value of a given input floating number or expression


SELECT CEILING(4.1);

The result of above query is:

5

SQL CEILING() Function More Example

Input Value Result
Ceiling(5.3) 6
Ceiling(6.8) 7
Ceiling(-7.7) -7
Ceiling((-4 * 1.2) + 3) -1

Sample Database Table - Customer

CID CName Balance City
111 Suresh Babu 128.66 Nasik
222 Suresh Kumar -55.23 Nasik
333 Vinoth Kumar -233.85 Chennai
444 Azagu Varshith 560 Madurai
555 Haris Karthik 673.88 Bangalore
666 Kishor Kumar -188.63 Chennai

SQL CEILING() Example - With Table Column

The following SQL SELECT statement find the next whole integer(without fraction digits) value of a given table column "Balance" from the "Customer" table:


SELECT CName, 
CEILING(Balance) As 'New Balance' 
FROM Customer;

The result of above query is:

CName New Balance
Suresh Babu 129
Suresh Kumar -55
Vinoth Kumar -233
Azagu Varshith 560
Haris Karthik 674
Kishor Kumar -188

SQL CEILING() Example - Using WHERE Clause

The following SQL SELECT statement find the next whole integer value of a given input number for creating condition (greater than the given value) on table column "Balance" from the "Customer" table:


SELECT CID, CName, Balance 
FROM Customer
WHERE Balance > CEILING(128.50);

The result of above query is:

CID CName Balance
444 Azagu Varshith 560
555 Haris Karthik 673.88

Note: The Ceiling(128.50) function will return a value of "129". So now the result set contains and display records or rows which has balance more than "129" on column "Balance" from the "Customer" table.


Search Keys

  • round number sql
  • sql decimal
  • sql 2 decimal places
  • sql round 2 decimal places
  • sql round to 2 decimals
  • sql roundup to 2 decimal places
  • truncate decimal sql
  • mssql ceiling