Oracle ROUND (Date) Function: A Simple Guide

The ROUND function in Oracle SQL is not just for numbers; it's also a powerful tool for DATE and TIMESTAMP values. When used with dates, ROUND "rounds" a datetime value to the nearest specified unit of time.

This is different from TRUNC (which always rounds down) and CEIL (which always rounds up). ROUND will go up or down to whichever unit is "closest."

What is the ROUND Function for Dates?

The ROUND(datetime, fmt) function takes a DATE or TIMESTAMP and returns a DATE value rounded to the nearest unit specified by the format model fmt.

The "midpoint" rule applies: if a date is exactly halfway to the next unit, Oracle rounds up.

• Rounding to 'DAY': 10:00 AM on a given day rounds down to 00:00:00 of that same day. 02:00 PM (14:00) rounds up to 00:00:00 of the next day (since noon is the midpoint).
• Rounding to 'MONTH': The 15th of the month is the midpoint. 01-JUL-2025 to 15-JUL-2025 will round down to 01-JUL-2025. 16-JUL-2025 and later will round up to 01-AUG-2025.
• Rounding to 'YEAR': July 1st is the midpoint. Any date on or after July 1st will round up to January 1st of the next year.

ROUND (Date) Function Syntax

The syntax for ROUND (Date) is:

ROUND(datetime, [fmt])

Let's break that down:

• datetime: The date or timestamp value (or column) you want to round.
• [fmt] (Optional): The "format model" or unit you want to round to.
  • If omitted, this defaults to 'DD', which rounds to the nearest day (based on noon).
  • Common fmt models:
    • 'YYYY' or 'YEAR': Rounds to the nearest year (midpoint is July 1st).
    • 'MM' or 'MONTH': Rounds to the nearest month (midpoint is the 16th day).
    • 'DD': Rounds to the nearest day (midpoint is noon).

A Note on Viewing Results

To see the full date and time in your results, you may need to run this command in your SQL session first:

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

Oracle ROUND (Date) Function Examples

Here are two practical examples of how to use ROUND.

Example 1: Rounding to the Nearest Year with ROUND

This example shows how rounding by 'YEAR' works. Since '27-OCT-2025' is after the midpoint of the year (July 1st), it will be rounded up to the first day of the next year.

Query:

SELECT
  ROUND(TO_DATE('27-OCT-2025', 'DD-MON-YYYY'), 'YEAR') AS "Next_Year"
FROM DUAL;

Result:

Next_Year
--------------------
01-JAN-2026 00:00:00

Example 2: Rounding to the Nearest Day with ROUND (Default)

This example shows the default behavior (rounding to the nearest day). The midpoint is noon (12:00 PM).

Query:

WITH times AS (
  SELECT TO_DATE('10-NOV-2025 10:00:00', 'DD-MON-YYYY HH24:MI:SS') AS "Morning_Time" FROM DUAL
  UNION ALL
  SELECT TO_DATE('10-NOV-2025 14:00:00', 'DD-MON-YYYY HH24:MI:SS') AS "Afternoon_Time" FROM DUAL
)
SELECT
  "Morning_Time",
  ROUND("Morning_Time") AS "Rounded_Morning",
  "Afternoon_Time",
  ROUND("Afternoon_Time") AS "Rounded_Afternoon"
FROM times;

Result:

Morning_Time         Rounded_Morning      Afternoon_Time       Rounded_Afternoon
-------------------- -------------------- -------------------- --------------------
10-NOV-2025 10:00:00 10-NOV-2025 00:00:00 10-NOV-2025 14:00:00 11-NOV-2025 00:00:00

Notice how the 10:00 AM time rounded down to the start of Nov 10th, while the 14:00 (2:00 PM) time rounded up to the start of Nov 11th.