Round Off Values Using Ceiling and Floor in SQL
Ceiling Function
CEILING() Function return the smallest integer value that is equal to or greater than the argument value. The arguments can be either positive or negative.
In the below example, 55.99 is the positive argument value and ceiling function will return the 56 because it the smallest integer value that is equal to or greater than the argument value 55.99.
Similarly, -55.99 is the negative argument value and it will return -55 because it is the smallest integer integer value which is equal to or greater than the argument value -55.99.
Ceiling Function with positive argument value
SELECT CEILING(55.99) AS CeilingValue

Ceiling Function with positive argument value and variable
DECLARE @PositiveValue FLOAT SET @PositiveValue = 99.1 SELECT CEILING(@PositiveValue) AS CeilingValue

Ceiling Function with negative argument value
SELECT CEILING(-55.99) AS CeilingValue

Ceiling Function with negative argument value and variable
DECLARE @NegativeValue FLOAT SET @NegativeValue = -99.1 SELECT CEILING(@NegativeValue) AS CeilingValue

Floor Function
FLOOR() Function return the largest value that is equal to or less than the argument value. The arguments can be either positive or negative.
In below example, 55.99 is a positive value and it will return 55 because 55 is the largest value that is equal to or less than argument value 55.99.
Similarly, -55.99 is the negative value and it will return -56
because -56 is the largest negative value which is equal to or less than the argument value -55.99.
Floor Function with positive argument value
SELECT FLOOR(55.99) AS FloorValue

Floor Function with positive argument value and variable
DECLARE @PositiveValue FLOAT SET @PositiveValue = 99.1 SELECT Floor(@PositiveValue) AS FloorValue

Floor Function with negative argument value
SELECT FLOOR(-55.99) AS FloorValue

Floor Function with negative argument value and variable
DECLARE @NegativeValue FLOAT SET @NegativeValue = -99.1 SELECT Floor(@NegativeValue) AS FloorValue

Recommended Readings
- Advanced SQL Queries For Practice With Solution
- SQL Queries For Practice With Solution
- SQL Interview Questions and Answers
- STORED PROCEDURE in SQL Server
- How To Join Tables Data in SQL Server
- How to use Transaction in SQL Stored Procedure
- Difference Between IN and NOT IN Operators in SQL
- How To Modify Date in SQL Using DATEADD
- How To Get Year From Date in SQL Server
- How To Get Month From Date in SQL Server
- How To Get Day From Date in SQL Server
- How To Use ROW_NUMBER Function in SQL
- Date and Time Functions in SQL Server
- How To Find Nth Highest Salary in SQL Server
- How to Backup Table Using SELECT INTO Statement
- How To Use HAVING Clause in SQL Server
- Aggregate Functions in SQL Server
- How To Group Data Using Group By in SQL Server
- How To Truncate Table in SQL Server
- How To Delete Data From Table in SQL Server
- How To Update Table Data in SQL Server
- How To Sort Data Using Order By Clause in SQL
- How To Select Distinct Records in SQL Server
- How to Filter Data From Table in SQL Server
- Round Off Values Using Ceiling and Floor in SQL
- How To Find Square Root Of A Number in SQL Server
- How To Select Data From Table in SQL Server
- How To Insert Data in SQL Server Table
- How To Add NOT NULL Constraint in SQL Server
- How To Add Check Constraint on SQL Server Table
- How To Add Default Constraint on SQL Server Table
- Unique Key Constraint in SQL Server
- How to add Foreign Key Constraint in SQL Server
- How To Add Identity To SQL Server Table Column
- How to add Primary Key Constraint in SQL Server
- How To Create Alter and Drop Table in SQL Server
- How To Create Alter and Drop Database in SQL