SQL CEILING() Function

The SQL CEILING() is a function, and return next whole integer(no fractional digits) value that is equal to or greater than a given input floating number from query result.

The SQL CEILING() function will always goes to the next whole whole integer value.

Ex:

• Ceiling(4) = result is "4"
• Ceiling(2.87) = result is "3"
• Ceiling(6.12) = result is "7"
• Ceiling(1.9) = result is "2"

The SQL Ceiling() function is supports only numeric column or an numeric field based expression.

It can be used in SELECT statement as well in where clause.

Main Article :- Sql difference between CEILING() and FLOOR() Functions

1. FLOOR() Function:

With one exception, the SQL Floor function is equivalent to the CEILING function. It gives the smallest and greatest integers that are bigger than or equal to the numeric expression. It also accepts one value.

Syntax:

FLOOR(number)

• Parameter: Required. A numeric value.
• Number: It is a numeric value.
• Returns: It returns the integer value.

Example 1: When the argument holds a positive number.

SELECT FLOOR(21.53);

Output:

Example 2: When the argument holds a negative number.

SELECT FLOOR(-21.53);

Output:

2. CEILING() Function:

To evaluate the value and return the smallest integer larger than or equal to the provided numeric expression, use the SQL CEILING function. There is only one value that it accepts.

Syntax:

CEILING(number)

• Parameter: Required. A numeric value.
• Number: It is a numeric value.
• Returns: It returns the integer value.

Example 1: When the argument holds a positive number.

SELECT CEILING(21.53);

Output:

Example 2: When the argument holds a negative number.

SELECT CEILING(-21.53);

Output:

Example 1: Let's use a decimal data type variable in this example and check the output for varying length precision.

Use the positive length for the decimal data type in the following query.

DECLARE @value DECIMAL(10, 2);
SET @value = 12.07;
SELECT ROUND(@value, 1);  
SELECT ROUND(@value, 2);
SELECT ROUND(@value, 3);

In this example, we can see that with decimal values round up to the nearest value as per the length.

Example 2: In the following query, use the negative length for the decimal data type.

DECLARE @value DECIMAL(10, 2);
SET @value = 12.07;
SELECT ROUND(@value, -1)
SELECT ROUND(@value, -2) 
SELECT ROUND(@value, -3)

The output is rounded to the nearest value, as can be seen. If the length is insufficient, it will also return a value of zero.

Example 3: SQL Server rounding functions CEILING and FLOOR for decimal data types:

DECLARE @value DECIMAL(10, 2);
SET @value = 12.07;
SELECT CEILING(@value)   
SELECT FLOOR(@value)

The greatest integer equal to or less than n is returned by FLOOR. The number n can always be expressed as the sum of a positive fraction f and an integer k, with 0<=f  < 1 and n = k + f. The number k is the value of FLOOR. As a result, the value of FLOOR is n if and only if n is an integer.

Any numeric data type or any nonnumeric data type that can be implicitly transformed to a numeric data type is passed as an input to this method. The function returns the same data type as the argument's numeric data type.

Example 1: The following SQL statement can be used to acquire the rounded down to the nearest integer value of -17.36 from the DUAL table:

SELECT FLOOR(-17.36) 
FROM dual; 

Output:

Example 2: The following example applies the FLOOR() function to a negative number.

SELECT FLOOR(-1.59);

Output:

The largest integer which is less than or equal to -1.59 is 2, therefore, the FLOOR() function returned -2.

Example 3: The following example returns the largest integer equal to or less than 15.7:

SELECT FLOOR(15.7) "Floor"
  FROM Team;

Output:

Depends on whether you're using SQL Server, Oracle, MySQL, or PostgreSQL, you might wish to use the CEIL or CEILING functions in SQL Server to round up or down to the nearest integer.

Example 1: To round up in Oracle, MySQL, andPostgreSQL, utilise the CEIL() or CEILING() functions.

SELECT CEIL(5.2)
SELECT CEILING(5.2)

Output:

Example 2: Here’s a SQL query that demonstrates the usage of ROUND,CEILING and FLOOR function in SQL server.

DECLARE @input decimal(18,5)

SET @input = 78.78912

SELECT CEILING(@input) CeilingValue
SELECT FLOOR(@input) FloorValue
SELECT ROUND(@input, 2) RoundValue

We were able to effectively use the CEILING and FLOOR functions to acquire the rounded up or rounded down value in the above query. Remember that the CEILING and FLOOR functions are not the same as the ROUND function, which employs the rounding(ceiling/floor) function behind the scenes.

Example 3: I'm looking for the nearest thousand number in SQL Server 2005. For example, if I give 460, the output is 1000; if I give 1000, the output is 1000; if I give 1001, the output is 2000; if I give 1550, the output is 2000; and if I give 2001, the output is 3000. The SQL Server CEILING function can be used to do this.

select mynumber
     , mynumber/1000.0               as dividedby1000
     , ceiling(mynumber/1000.0)      as ceilingresult
     , ceiling(mynumber/1000.0)*1000 as myfinalnumber
from mynumbers

CEILING returns the smallest integer that is either equal to or larger than the given value. To acquire the "rounded up to next 1000" value you sought, we'll need to use a sophisticated formula employing CEILING. The "secret" (if you want to call it that) is to divide by 1000.0 before applying the CEILING, which forces a decimal result.

The above query also demonstrates how to test a complex expression like this. Build it up in distinct columns, one step at a time.

The results of the query on the data you provided are:

CEILING returns the integer value closest to numeric-expression that is greater than or equal to it. The scale of the returned value is 0. CEILING returns NULL when numeric-expression is a NULL value, an empty string ("), or any nonnumeric string.

CEIL(timestamp_expr TO unit) returns a new timestamp after rounding up a timestamp. SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR can all be used as units.

CEILING can be used as an ODBC scalar function or as a SQL general function (using the curly brace syntax).

Example 1: The following example returns 101 because the nearest integer of 100.49 is 101.

SELECT CEIL(100.49);

Output:

Example 2: The following statement returns -100 because it is the nearest integer value of -100.49:

SELECT CEIL(-100.49);

Output:

Example 3: The following statement returns 101 because it is the nearest integer of 100.51:

SELECT CEIL(100.51);

Output:

Example 4: See the employees and departments tables in the sample database.

emp_dept_tables

The following example uses the CEIL function to round the average salary of employees in each department.

SELECT department_name, CEIL(AVG(salary)) AS average_salary
FROM employees e
INNER JOIN departments d on d.department_id = e.department_id
GROUP BY department_name
ORDER BY department_name;

Output:

Example 5: First, we utilised it to retrieve the variable @i's closest integer value. It means that -208.45 is the closest value to -208.45. We utilised this Function directly on the integer value in the following SQL Server statement.

In the last statement, We used the CEILING Function directly on the multiple values. It means CEILING (2.45 + 7.55 – 14.88) = -4.88 = – 4:

DECLARE @i float
SET @i = -208.45

SELECT CEILING(@i)AS [SQLCEILING]

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

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

Example 6: The following examples show how CEILING converts a fraction to its ceiling integer:

SELECT CEILING(167.111) AS CeilingNum1,
 CEILING(167.456) AS CeilingNum2,
 CEILING(167.999) AS CeilingNum3

Output:

SELECT {fn CEILING(167.00)} AS CeilingNum1,
 {fn CEILING(167.00)} AS CeilingNum2

Output:

SELECT CEILING(-167.111) AS CeilingNum1,
       CEILING(-167.456) AS CeilingNum2,
       CEILING(-167.999) AS CeilingNum3

Output:

SELECT CEILING(-167.00) AS CeilingNum 

Output:

Example 1: To satisfy the DATE type, ROUND and TRUNC are additionally overloaded. They round or truncate to the DAY by default, resulting in a DATE value with a time of 00:00:00. The second option can be used to alter the default by specifying a subset of the text formatting substrings.

SELECT d, ROUND(d), TRUNC(d)
FROM (SELECT TO_DATE('2019-08-20 08:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL
UNION ALL
SELECT TO_DATE('2019-08-20 18:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL);

Output:

SELECT t, ROUND(t), TRUNC(t)
FROM (SELECT TO_timestamp('2019-08-20 08:30', 'yyyy-mm-dd hh24:mi') t FROM DUAL
UNION ALL
SELECT TO_timestamp('2019-08-20 18:30', 'yyyy-mm-dd hh24:mi') t FROM DUAL);

Output:

SELECT t, ROUND(t), TRUNC(t)
FROM (SELECT TO_TIMESTAMP_TZ('2019-08-20 08:30 US/Eastern', 'yyyy-mm-dd hh24:mi TZR') t FROM DUAL
UNION ALL
SELECT TO_TIMESTAMP_TZ('2019-08-20 18:30 US/Eastern', 'yyyy-mm-dd hh24:mi TZR') t FROM DUAL);

Output:

It's worth repeating: both ROUND and TRUNC will generate a DATE result, as shown in the timestamp results above. There is no native method that returns the same output type as the input type when rounding or truncating timestamps (with or without time zones). As a result, the remaining examples will only use the DATE type as input, as timestamps would have similar results.

Example 2: Round/Trunc to the week:

SELECT d, ROUND(d,'d'), TRUNC(d,'d')
FROM (SELECT TO_DATE('2019-08-20 08:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL
UNION ALL
SELECT TO_DATE('2019-08-22 18:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL);

Output:

Example 3: Round/Trunc to the year:

SELECT d, ROUND(d,'y'), TRUNC(d,'y')
FROM (SELECT TO_DATE('2019-08-20 08:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL
UNION ALL
SELECT TO_DATE('2019-08-22 18:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL);

Output:

Example 4: Round/Trunc to the hour:

SELECT d, ROUND(d,'hh'), TRUNC(d,'hh')
FROM (SELECT TO_DATE('2019-08-10 08:25', 'yyyy-mm-dd hh24:mi') d FROM DUAL
UNION ALL
SELECT TO_DATE('2019-08-22 18:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL);

Output:

All of the outcomes to this far have been in normal and built-in increments: days, years, hours; you can also handle quarters, ISO weeks, minutes, and so on. You may need to round dates to other increments, such as half-hour, 10-minute, or 15-minutes, on occasion. When individuals say "round" in these situations, they're usually referring to a CEILing or FLOOR value.

Example 5: "Round" to the half-hour:

SELECT d,
 TRUNC(d, 'hh24') + CEIL((TO_CHAR(d, 'mi') - 29) / 30) * 30 / 1440 round_down,
 TRUNC(d, 'hh24') + FLOOR((TO_CHAR(d, 'mi') + 30) / 30) * 30 / 1440 round_up
 FROM (    SELECT TO_DATE('2019-08-10 08:20', 'yyyy-mm-dd hh24:mi') + LEVEL*7 / 1440 d
 FROM DUAL
 CONNECT BY LEVEL <= 10);

Output:

Example 6: "Round" to the quarter-hours:

SELECT d,
TRUNC(d, 'hh24') + CEIL((TO_CHAR(d, 'mi') - 14) / 15) * 15 / 1440 round_down,
TRUNC(d, 'hh24') + FLOOR((TO_CHAR(d, 'mi') + 15) / 15) * 15 / 1440 round_up
FROM (SELECT TO_DATE('2019-08-10 08:20', 'yyyy-mm-dd hh24:mi') + LEVEL*7 / 1440 d
FROM DUAL
CONNECT BY LEVEL <= 10);

Output:

While ROUND, TRUNC, CEIL, and FLOOR are basic SQL functions, maybe this tutorial has shown you some alternative ways to use them in your applications.

Example 7: Post navigation

To implement the DATE type, ROUND and TRUNC are additionally overloaded. They round or truncate to the DAY by default, resulting in a DATE value with a time of 00:00:00. The second option can be used to alter the default by specifying a subset of the text formatting substrings.

SELECT d, ROUND(d), TRUNC(d)
FROM (SELECT TO_DATE('2019-08-20 08:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL
UNION ALL
SELECT TO_DATE('2019-08-20 18:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL);

Output:

SELECT t, ROUND(t), TRUNC(t)
FROM (SELECT TO_timestamp('2019-08-20 08:30', 'yyyy-mm-dd hh24:mi') t FROM DUAL
UNION ALL
SELECT TO_timestamp('2019-08-20 18:30', 'yyyy-mm-dd hh24:mi') t FROM DUAL);

Output:

SELECT t, ROUND(t), TRUNC(t)
FROM (SELECT TO_TIMESTAMP_TZ('2019-08-20 08:30 US/Eastern', 'yyyy-mm-dd hh24:mi TZR') t FROM DUAL
UNION ALL
SELECT TO_TIMESTAMP_TZ('2019-08-20 18:30 US/Eastern', 'yyyy-mm-dd hh24:mi TZR') t FROM DUAL);

Output:

It's worth repeating: both ROUND and TRUNC will generate a DATE result, as shown in the timestamp results above. There is no native method that returns the same output type as the input type when rounding or truncating timestamps (with or without time zones). As a result, the remaining examples will only use the DATE type as input, as timestamps would have similar results.

Example 8: Round/Trunc to the week

SELECT d, ROUND(d,'d'), TRUNC(d,'d')
 FROM (SELECT TO_DATE('2019-08-20 08:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL
 UNION ALL
 SELECT TO_DATE('2019-08-22 18:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL);

Output:

Example 9: Round/Trunc to the year:

SELECT d, ROUND(d,'y'), TRUNC(d,'y')
FROM (SELECT TO_DATE('2019-08-20 08:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL
UNION ALL
SELECT TO_DATE('2019-08-22 18:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL);

Output:

Example 10: Round/Trunc to the hour

SELECT d, ROUND(d,'hh'), TRUNC(d,'hh')
FROM (SELECT TO_DATE('2019-08-10 08:25', 'yyyy-mm-dd hh24:mi') d FROM DUAL
UNION ALL
SELECT TO_DATE('2019-08-22 18:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL);

Output:

All of the outcomes to this far have been in normal and built-in increments: days, years, hours; you can also handle quarters, ISO weeks, minutes, and so on. You may need to round dates to other increments, such as half-hour, 10-minute, or 15-minutes, on occasion. When individuals say "round" in these situations, they're usually referring to a CEILing or FLOOR value.

Example 11: "Round" to the half-hour

SELECT d,
TRUNC(d, 'hh24') + CEIL((TO_CHAR(d, 'mi') - 29) / 30) * 30 / 1440 round_down,
TRUNC(d, 'hh24') + FLOOR((TO_CHAR(d, 'mi') + 30) / 30) * 30 / 1440 round_up
FROM (SELECT TO_DATE('2019-08-10 08:20', 'yyyy-mm-dd hh24:mi') + LEVEL*7 / 1440 d
FROM DUAL
CONNECT BY LEVEL <= 10);

Output:

Example 12: "Round" to the quarter-hours

SELECT d,
TRUNC(d, 'hh24') + CEIL((TO_CHAR(d, 'mi') - 14) / 15) * 15 / 1440 round_down,
TRUNC(d, 'hh24') + FLOOR((TO_CHAR(d, 'mi') + 15) / 15) * 15 / 1440 round_up
FROM (    SELECT TO_DATE('2019-08-10 08:20', 'yyyy-mm-dd hh24:mi') + LEVEL*7 / 1440 d		           
FROM DUAL
CONNECT BY LEVEL <= 10);

Output:

While ROUND, TRUNC, CEIL, and FLOOR are all standard SQL functions, hopefully this guide has shown you some additional ways to use them in your applications.