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.

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

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

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.

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

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

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

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

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

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

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.

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 with 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
Categories SQL