SQL Server CEILING

The SQL Server CEILING function is one of the Mathematical Function used to return the closest integer value which is greater than or equal to the specified expression.

SQL Server CEILING Function Syntax

The basic syntax of the SQL Server CEILING Function is:

SELECT CEILING (Numeric_Expression)
FROM [Source]

In this article we will show you, How to write CEILING Function in SQL Server with example. For this, We are going to use the below shown data

SQL Server CEILING Function Example 1

The CEILING Function in SQL is used to return the closest integer value which is greater than or equal to given numeric value. The following query will show multiple ways to use CEILING function.

T-SQL CODE

DECLARE @i float
SET @i = -208.45

SELECT CEILING(@i)AS [SQL CEILING]

-- Calculating CEILING directly
SELECT CEILING(0.24)AS [SQL CEILING]

-- Calculating CEILING directly
SELECT CEILING(2.45 + 7.55 - 14.88)AS [SQL CEILING]

OUTPUT

Analysis

Below lines of code is used to declare float variable and assigning the value.

DECLARE @i float
SET @i = -208.45

In the below statement, We used CEILING function to find the closest integer value of the variable @i (It means CEILING(-208.45)). We also assigned new name to that result as ‘SQL CEILING’ using ALIAS Column.

SELECT CEILING(@i)AS [SQL CEILING]

In the next statement, We used the CEILING Function directly on the int value.

-- Calculating CEILING directly
SELECT CEILING(0.24)AS [SQL CEILING]

In the below statement, We used the CEILING Function directly on the multiple values.

-- Calculating CEILING directly
SELECT CEILING(2.45 + 7.55 - 14.88)AS [SQL CEILING]

It means

CEILING (2.45 + 7.55 – 14.88)

CEILING (-4.88) = – 4

SQL Server CEILING Function Example 2

In this example, We are going to find the closet integer values for all the records present in [Service Grade] using CEILING Function.

T-SQL CODE

SELECT [EnglishProductName]
      ,[Color]
      ,CEILING([StandardCost]) AS COST
      ,CEILING([SalesAmount]) AS SALES
      ,CEILING([TaxAmt]) AS TAX
      ,CEILING([Service Grade]) AS Grade
  FROM [Mathemetical Functions]

OUTPUT

Thank You for Visiting Our Blog