Menu

Oracle TRUNC(date): Day, Month, Year and ISO Week Examples

Updated on

Oracle TRUNC(datetime) truncates a date or datetime value to the beginning of a specified calendar unit. Without a format argument, it removes the time of day and returns midnight on the same date. With a format model, it can return the beginning of a month, year, quarter, or week.

Important: Oracle’s datetime form of TRUNC() returns a value of type DATE, even when you pass a TIMESTAMP. This is different from the numeric form TRUNC(number).

Syntax

TRUNC(datetime_expression)
TRUNC(datetime_expression, format_model)

The optional format_model selects the truncation unit. When omitted, Oracle uses 'DD' (day). Common models include:

Format model Truncates to Example result
'DD' (default) Midnight of the same day 2026-06-18
'MM' or 'MONTH' First day of the month 2026-06-01
'YYYY' or 'YEAR' First day of the calendar year 2026-01-01
'Q' First day of the quarter 2026-04-01
'IW' Monday of the ISO 8601 week Week may start in the prior calendar year
'WW' Start of the week based on the year’s first day May differ from ISO week
'HH24' Start of the hour Minutes and seconds become zero
'MI' Start of the minute Seconds become zero

Oracle uses Gregorian-calendar rules for date truncation, regardless of NLS_CALENDAR. Week formats differ: 'IW' follows ISO weeks; 'D', 'DAY' and 'DY' can depend on NLS_TERRITORY.

Examples

Remove the time portion of a datetime

SELECT TO_CHAR(
    TRUNC(TO_DATE('2026-06-18 14:29:35', 'YYYY-MM-DD HH24:MI:SS')),
    'YYYY-MM-DD HH24:MI:SS'
) AS start_of_day
FROM dual;
START_OF_DAY
-------------------
2026-06-18 00:00:00

TO_CHAR() is used to display a consistent result regardless of your session’s default date format. The returned value from TRUNC() is still an Oracle DATE, not a character string.

Find the first day of a month, quarter or year

SELECT
    TO_CHAR(TRUNC(DATE '2026-06-18', 'MM'), 'YYYY-MM-DD') AS month_start,
    TO_CHAR(TRUNC(DATE '2026-06-18', 'Q'), 'YYYY-MM-DD') AS quarter_start,
    TO_CHAR(TRUNC(DATE '2026-06-18', 'YEAR'), 'YYYY-MM-DD') AS year_start
FROM dual;
MONTH_START  QUARTER_START  YEAR_START
-----------  -------------  ----------
2026-06-01   2026-04-01     2026-01-01

Use 'MM' for monthly reports and 'Q' for quarterly reports. 'YEAR' follows the calendar year, not an ISO week-numbering year.

ISO weeks can cross calendar-year boundaries

SELECT
    TO_CHAR(TRUNC(DATE '2026-01-01', 'IW'), 'YYYY-MM-DD') AS iso_week_start,
    TO_CHAR(TRUNC(DATE '2026-01-01', 'WW'), 'YYYY-MM-DD') AS year_week_start
FROM dual;
ISO_WEEK_START  YEAR_WEEK_START
--------------  ---------------
2025-12-29      2026-01-01

January 1, 2026 belongs to an ISO week that begins on Monday, December 29, 2025. 'IW' and 'WW' are not interchangeable.

What happens to TIMESTAMP input?

SELECT TO_CHAR(
    TRUNC(TIMESTAMP '2026-06-18 14:29:35.123456', 'DD'),
    'YYYY-MM-DD HH24:MI:SS'
) AS truncated_timestamp
FROM dual;
TRUNCATED_TIMESTAMP
-------------------
2026-06-18 00:00:00

The datetime overload returns DATE, so fractional seconds are not preserved. If your application needs to keep a TIMESTAMP type or specific time-zone semantics, verify the required conversions instead of assuming TRUNC() preserves the input type.

Filtering an indexed date column

This predicate is easy to read:

SELECT order_id
FROM orders
WHERE TRUNC(created_at) = DATE '2026-06-18';

But applying a function to the table column may make a conventional index on created_at less useful. For a DATE or TIMESTAMP column, a half-open range often makes better use of an ordinary index:

SELECT order_id
FROM orders
WHERE created_at >= DATE '2026-06-18'
  AND created_at < DATE '2026-06-19';

The range includes all times on June 18 while excluding midnight on June 19. Actual performance depends on the optimizer, indexes and table statistics; use an execution plan to verify. If you intentionally index TRUNC(created_at) as a function-based expression, the first form may also be appropriate.

Frequently asked questions

Is TRUNC(date) the same as TRUNC(number)?

No. The datetime form truncates to a calendar unit; the numeric form removes decimal digits. Oracle chooses the appropriate overload from the input expression.

Why does TRUNC(date) display a time?

The return value is an Oracle DATE, which stores a time-of-day component. Truncating to day sets that component to midnight; how it appears depends on your session’s date format.

Does TRUNC(TIMESTAMP) return TIMESTAMP?

No. The datetime form returns DATE. Do not rely on it to retain fractional seconds.

What is the difference between ‘IW’ and ‘WW’?

'IW' begins on Monday according to ISO 8601 week rules. 'WW' counts weeks from the year’s first day, which can produce a different date.

See the Oracle TRUNC(datetime) reference and Oracle datetime format models.