{"id":3695,"date":"2024-04-03T22:04:06","date_gmt":"2024-04-03T15:04:06","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3695"},"modified":"2024-04-04T08:07:35","modified_gmt":"2024-04-04T01:07:35","slug":"sql-server-ceiling","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-math-functions\/sql-server-ceiling\/","title":{"rendered":"SQL Server CEILING() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server CEILING<\/code> function to round up a number to the nearest integer greater than or equal to the input number.<\/p>\n\n\n\n

SQL Server CEILING function syntax #<\/a><\/h2>\n\n\n\n

The CEILING()<\/code> function allows you to round up a number to the nearest integer that is greater than or equal to an input number.<\/p>\n\n\n\n

Here’s the syntax of the CEILING()<\/code> function:<\/p>\n\n\n

CEILING (number)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the number is a literal number or a numeric expression that you want to round.<\/p>\n\n\n\n
The function returns a number whose type depends on the type of the input number. The following table illustrates the return type of the function based on the input type:<\/p>\n\n\n\n
Input type<\/th>Return type<\/th><\/tr><\/thead>
float, <\/strong>real<\/td>float<\/td><\/tr>
decimal(p, s)<\/td>decimal(38, s)<\/td><\/tr>
int, <\/strong>smallint, <\/strong>tinyint<\/td>int<\/td><\/tr>
bigint<\/td>bigint<\/td><\/tr>
money, smallmoney<\/td>money<\/td><\/tr>
bit<\/td>float<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
If the number is NULL<\/code>, the function returns NULL<\/code>.<\/p>\n\n\n\n
SQL Server CEILING() function examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples of using the CEILING()<\/code> function.<\/p>\n\n\n\n
1) Using SQL Server CEILING() function with positive numbers #<\/a><\/h3>\n\n\n\n
The following example uses the CEILING()<\/code> function to round up positive numbers:<\/p>\n\n\n
SELECT<\/span>\n  CEILING<\/span>(11.51<\/span>) x,\n  CEILING<\/span>(11.25<\/span>) y;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
x  | y\n---+----<\/span>\n12 | 12<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using the CEILING function with negative numbers #<\/a><\/h3>\n\n\n\n
The following statement uses the CEILING()<\/code> function to round up negative numbers:<\/p>\n\n\n
SELECT<\/span>\n  CEILING<\/span>(-11.51<\/span>) x,\n  CEILING<\/span>(-11.25<\/span>) y;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
x   | y\n----+-----<\/span>\n-11 | -11\n(1 row)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Using the CEILING function with money values #<\/a><\/h3>\n\n\n\n
The following example uses the CEILING()<\/code> function to round up the values of the money type:<\/p>\n\n\n
SELECT<\/span>\n  CEILING<\/span>($99.5<\/span>) amount,\n  CEILING<\/span>($9.91<\/span>) tax;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
amount   | tax\n---------+----------<\/span>\n100.0000 | 10.0000\n(1 row)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
4) Using the CEILING function with table data #<\/a><\/h3>\n\n\n\n
We’ll use the production.products<\/code> table from the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The following statement uses the CEILING()<\/code> function to round up the average<\/a> price by category id:<\/p>\n\n\n
SELECT<\/span>\n  category_id,\n  CEILING<\/span>(AVG<\/span>(list_price)) average_price\nFROM<\/span>\n  production.products\nGROUP<\/span> BY<\/span>\n  category_id;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
category_id | average_price\n------------+---------------<\/span>\n1           | 288\n2           | 683\n3           | 731\n4           | 2543\n5           | 3282\n6           | 1650\n7           | 3176\n(7 rows)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the CEILING()<\/code> function to round up a number to the nearest integer greater than or equal to the input number.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t