Oracle CEIL (Date) Function: A Simple Guide

The CEIL function in Oracle SQL can be used on numbers, but it also has a powerful version for DATE and TIMESTAMP values. In this context, CEIL "rounds up" a datetime value to the next specified unit of time.

This is the opposite of the TRUNC function, which just "cuts off" the time. CEIL is useful for finding the end of a time period or the start of the next one.

What is the CEIL Function for Dates?

The CEIL(datetime, fmt) function takes a DATE or TIMESTAMP and returns the smallest DATE value that is greater than or equal to it, based on the format model fmt.

In simple terms:

• CEIL(10:15 AM, 'HH') rounds up to 11:00 AM.
• CEIL('15-Mar-2025', 'MM') rounds up to 01-Apr-2025.

Important Exception: If the date is already at the start of the unit (e.g., CEIL('01-Mar-2025', 'MM')), it does not round up. It returns the same date.

The value returned is always a DATE data type, even if your input was a TIMESTAMP.

CEIL (Date) Function Syntax

The syntax for CEIL (Date) is:

CEIL(datetime, [fmt])

Let's break that down:

• datetime: The date or timestamp value (or column) you want to round up.
• [fmt] (Optional): The "format model" or unit you want to round up to.
  • If omitted, this defaults to 'DD', which rounds a datetime up to the start of the next day.
  • Common fmt models:
    • 'YYYY' or 'YEAR': Rounds up to the first day of the next year (Jan 1st).
    • 'MM' or 'MONTH': Rounds up to the first day of the next month (e.g., Apr 1st).
    • 'DD': Rounds up to the start of the next day (00:00:00).
    • 'HH' or 'HH24': Rounds up to the next hour (e.g., 10:15 becomes 11:00).

A Note on Viewing Results

To see the time portion in the results, you may need to run this command in your SQL session first, as the default Oracle date format often hides the time.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Oracle CEIL (Date) Function Examples

Here are two practical examples of how to use CEIL.

Example 1: Rounding a Timestamp to the Next Hour

This is a common use case for scheduling or time-blocking. Imagine you have a timestamp, and you want to find the start of the next hour.

Query:

SELECT
  TO_TIMESTAMP('2025-10-20 09:14:30', 'YYYY-MM-DD HH24:MI:SS') AS "Original_Time",
  CEIL(TO_TIMESTAMP('2025-10-20 09:14:30', 'YYYY-MM-DD HH24:MI:SS'), 'HH') AS "Next_Hour_Start"
FROM DUAL;

Result:

Original_Time                  Next_Hour_Start
------------------------------ --------------------
20-OCT-25 09.14.30.000000 AM    20-OCT-2025 10:00:00

Since the time 09:14:30 is after 09:00:00, it gets rounded up to 10:00:00.

Example 2: Rounding a Date to the Next Month

Let's say you have a project date, and you want to find the deadline, which is the start of the next month.

Query:

SELECT 
  DATE '2025-07-10' AS "Original_Date",
  CEIL(DATE '2025-07-10', 'MM') AS "Next_Month"
FROM DUAL;

Result:

Original_Date        Next_Month
-------------------- --------------------
10-JUL-2025 00:00:00 01-AUG-2025 00:00:00

Because the date 10-JUL is not the first day of the month, it rounds up to the first day of the next month (01-AUG).