SQL CEILING Function

The SQL Server CEILING function is a mathematical function used to return the closest (smallest) integer value, which is greater than or equal to the specified expression. For positive values, the return value of a CEILING function moves upwards away from zero and for negative numbers, it moves towards zero.

SQL CEILING Syntax

The syntax of the CEILING Function to display the nearest numeric value is as shown below.

SELECT CEILING(Numeric_Expression)
FROM [Source]

The given argument can be an integer, decimal, float, real, money, or appropriate numeric expression whose result belongs to the numeric data type category.

TIP: If the SQL Server CEILING function result doesn’t fit into the column, it will throw an arithmetic overflow.

Return Value: It returns the same data type as the argument. However, if the argument value is NULL, it returns NULL as an output.

SQL CEILING Function Example

The CEILING Function returns the closest integer value, which is greater than or equal to the given numeric argument value. The following list of examples helps you understand how to use the CEILING function with positive and negative values of different data types and expressions.

In this series of SQL Server CEILING function examples, we start this section with INT values. It can be a positive or negative integer.

Ceiling Positive and Negative Integers

Let me assign the positive number 25 as the CEILING function argument value. Since 25 is the smallest number greater than or equal to 25, it returns 25 as an output.

SELECT CEILING(25) AS r1;
25

Similarly, this time, we use the negative number (-25) as an input, and it returns -25 as the output.

SELECT CEILING(-25) AS r2;
-25

Ceiling Positive and Negative Decimal Values

When it comes to the INT values, the closest value of any number that is greater than or equal to itself will always return the same value. In this example, we will add the decimal values to show the difference.

SELECT CEILING(12.01) AS d1, 
	CEILING(12.90) AS d2, 
	CEILING(12.45) AS d3
d1	d2	d3
13	13	13

If you observe the result set, all three of them return 13 as the output. The SQL math CEILING function does not care how close the decimal values are; it simply returns the next (nearest) integer, i.e., 13 in this case.

Let’s add the negative symbol to the above query. Both -12.01 and -12.90 will return -12 as the output because -12 is the smallest int value that is greater than or equal to -12.01 and -12.90.

SELECT CEILING(-12.01) AS d1, 
	CEILING(-12.90) AS d2
d1	d2
-12	-12

Ceiling Zeros

This example uses the CEILING on zeros. To do so, let me add the zero values as an argument with decimal values.

SELECT CEILING(0.00) AS Zero, 
	CEILING(0.05) AS PosDec, 
	CEILING(-0.05) AS NegDec
Zero	PosDec	NegDec
0	1	0

As you can notice, 0.00 returns 0, -0.05 returns 0 because it (0 > -0.0.5) is the greatest number, and 0.05 returns 1 (largest). Remember, 0.01 will also return 1.

Ceiling Float Value

In this SQL Server example, we used it to find the closest integer value of the variable @i. It means -208.453289 closest value is -208.

DECLARE @i float
SET @i = -208.453289
SELECT CEILING(@i)AS f1
-208

Ceiling Numeric Data type

The following example declares a numeric data type variable and uses the SQL CEILING function to find the nearest value.

DECLARE @i NUMERIC(10, 5)
SET @i = 100.1453289
SELECT CEILING(@i)AS n1
101

Ceiling Numeric Expression

Apart from using a single value as an argument, you can write any numeric expression that returns a numeric data type value. In this statement, we use the SQL CEILING Function directly on multiple values by writing a numeric expression. It means (2.45 + 7.55 – 14.88) = -4.88 = – 4.

SELECT CEILING(2.45 + 7.55 - 14.88) AS exOUT
-4

Ceiling Money Values

This example uses the MONEY data type as the CEILING function argument.

SELECT CEILING($119.99) AS Amount, 
	CEILING(-$18.25) AS Discount
Amount	Discount
120.00	-18.00

Ceiling Scientific Notations

The SQL CEILING function also allows you to use scientific notations as the argument value to find the nearest integer.

In the query below, 1.23E-2 is equivalent to 0.0123, and the number greater than it is 1. Similarly, -1E-2 is equals to -0.01 and 9.81E1 means -98.1

SELECT CEILING(1.23E-2) AS c1, 
	CEILING(-1E-2) AS c2, 
	CEILING(-9.81E1) AS c3
c1	c2	c3
1	0	-98

Ceiling Mathematic Constants

Apart from scientific notations, such as the CEILING function argument, you can use the mathematical constants such as PI.

SELECT CEILING(PI())

As we already know, the PI value is 3.14159265358979, and its nearest value is 4.

4

Ceiling String Value

If you pass the string as the argument, SQL Server will implicitly convert it to a numeric value and apply the CEILING function to it.

SELECT CEILING('99.98') AS s1
100

When you pass a string text that can’t be converted to a numeric value, the following error will be thrown by the server.

SELECT CEILING('99.98A') AS s2
Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to float.

Ceiling NULL Values

If you pass the NULL value as an argument, the CEILING function will return NULL as the output.

SELECT CEILING(NULL)AS n1
NULL

SQL Server CEILING Function on Table

In all our previous examples, we used the CEILING function directly on numeric values or variables. However, you can use this CEILING function on table columns, and this example uses the [AdventureWorks2022] database.

The query below will find the closest integer values for all records in the SalesYTD and SalesLastYear columns from the [SalesTerritory] table using this Mathematical method.

SELECT [TerritoryID]
      ,[CountryRegionCode]
      ,[Group]
      ,[SalesYTD]
      ,CEILING(SalesYTD) AS YTDSale
      ,[SalesLastYear]
      ,CEILING(SalesLastYear) AS LastYSales
  FROM [Sales].[SalesTerritory]

TIP: Please refer to the FLOOR and ROUND functions to observe other options for rounding values.

We can easily combine the CEILING function with other functions, such as Aggregate functions or any mathematical calculations. The query below uses the AVG function to find the current and last year’s sales average. Next, the CEILING function returns the nearest integer value greater than the existing one for each row.

SELECT [Group]
    ,CEILING(AVG(SalesYTD)) AS YTDSale
    ,CEILING(AVG(SalesLastYear)) AS LastYSales
FROM [Sales].[SalesTerritory]
GROUP BY [Group]

Use SQL CEILING DATE and TIME

When working with date and time values, in some cases, it is important to display the nearest integer value instead of decimals. In such a case, use the CEILING function, but you can’t use it directly on DATE TIME data types. So, first use any date function to extract the numeric values and then apply the CEILING function.

Suppose you are maintaining a server that changes on an hourly and daily basis based on the subscription. In this case, you can use the DATEDIFF function to find the difference in hours and days. Next, use the CEILING function to show the nearest integer value.

  • Minutes Vs Bill Hours: Even though the user used the server for 3 Hours 25 Minutes, the total Billing will be for 4 hours.
  • Days Vs Bill Days: Even though the user used 8 hours of the server, he is charged for the entire day.
SELECT  [ID],[Location],[StartDate],[EndDate]
    ,DATEDIFF(MINUTE, StartDate, EndDate) AS Minutes
    ,CEILING(DATEDIFF(MINUTE, StartDate, EndDate) / 60.0) AS BillHours
    ,DATEDIFF(HOUR, StartDate, EndDate) / 24.0 AS Days
    ,CEILING(DATEDIFF(HOUR, StartDate, EndDate) / 24.0) AS BillDays
FROM [Servers]
SQL Server CEILING function Example

SQL CEILING to nearest 10

To round up to the given value to the nearest 10, we must divide the existing value (before ceiling) by 10. Next, apply the CEILING function and multiply the result by 10. For example, in the query below, we used the regular CEILING approach and the nearest 10 value.

DECLARE @i float
SET @i = 204.453289
SELECT CEILING(@i)AS Original, 
	CEILING( @i /10.0) * 10 AS Nearest10
Original	Nearest10
205	        210

This time, we use the same technique on the table columns for CEILING to the nearest 10.

SELECT [Group]
    ,CEILING(AVG(SalesYTD)) AS YTDSale
    ,CEILING(AVG(SalesYTD)/10.0) * 10 AS [YTDsales10]
    ,CEILING(AVG(SalesLastYear)) AS LastYSales
    ,CEILING(AVG(SalesLastYear) /10.0) * 10 AS [Ysales10]
FROM [Sales].[SalesTerritory]
GROUP BY [Group]
Group	YTDSale	YTDsales10	LastYSales	Ysales10
Europe	4530169.00	4530170	1780105.00	1780110
North America	5530482.00	5530490	4182749.00	4182750
Pacific	5977815.00	5977820	2278549.00	2278550

SQL CEILING to 2 decimal places

Similar to the above, we can multiply the existing value by 100, apply the CEILING function, and divide the result by 100 to round up the given value to 2 decimal places.

DECLARE @i float
SET @i = 204.453289
SELECT CEILING(@i)AS Original, 
	CEILING( @i * 100) / 100.0 AS TwoDecimals
Original	TwoDecimals
205	         204.46
Categories SQL