Oracle FLOOR (Date) Function: A Simple Guide

The FLOOR function in Oracle SQL can be used on numbers, but it also has a version for DATE and TIMESTAMP values. For dates, FLOOR "rounds down" the datetime to the unit you specify.

Here is the most important thing to know: for DATE and TIMESTAMP values, the FLOOR function is identical to the TRUNC (date) function. They are synonyms and can be used interchangeably.

What is the FLOOR Function for Dates?

The FLOOR(datetime, fmt) function takes a DATE or TIMESTAMP and "cuts it off" at the specified unit of time (fmt), returning the starting point of that unit.

In simple terms:

• FLOOR(10:45 AM, 'HH') returns 10:00 AM.
• FLOOR('15-Mar-2025', 'MM') returns 01-Mar-2025.

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

FLOOR (Date) Function Syntax

The syntax for FLOOR (Date) is:

FLOOR(datetime, [fmt])

Let's break that down:

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

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 FLOOR (Date) Function Examples

Here are two practical examples of how to use FLOOR.

Example 1: Rounding a Timestamp Down to the Hour with FLOOR

This example takes the current, exact timestamp (which includes fractional seconds) and rounds it down to the very beginning of the current hour.

Query:

SELECT 
  SYSTIMESTAMP AS "Exact_Time",
  FLOOR(SYSTIMESTAMP, 'HH') AS "Start_of_Hour"
FROM DUAL;

Result: (Assuming the query was run at 16:45:30)

Exact_Time                         Start_of_Hour
---------------------------------- --------------------
06-NOV-25 04.45.30.123456 PM +05:30 06-NOV-2025 16:00:00

Notice how 16:45:30 was rounded down to 16:00:00.

Example 2: Rounding a Date Down to the Month with FLOOR

This example takes a specific date and rounds it down to the first day of that same month.

Query:

SELECT 
  DATE '2025-07-28' AS "Original_Date",
  FLOOR(DATE '2025-07-28', 'MM') AS "Start_of_Month"
FROM DUAL;

Result:

Original_Date        Start_of_Month
-------------------- --------------------
28-JUL-2025 00:00:00 01-JUL-2025 00:00:00