How to Truncate a Date in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
TRUNC(date) strips the time down to midnight. Pass a second argument, TRUNC(date, 'MM'), to round down to the start of the month, year, or hour instead.
SELECT TRUNC(SYSDATE) AS today_midnight,
TRUNC(SYSDATE, 'MM') AS first_of_month,
TRUNC(SYSDATE, 'YYYY') AS first_of_year,
TRUNC(SYSDATE, 'HH') AS start_of_hour
FROM dual;The classic use: stripping time for comparisons
Because DATE columns store a time component even when you only care about the day, comparing order_date = TO_DATE('2026-08-25','YYYY-MM-DD') will silently miss every row logged after midnight. Wrapping the column in TRUNC fixes the comparison.
SELECT * FROM orders
WHERE TRUNC(order_date) = TO_DATE('2026-08-25', 'YYYY-MM-DD');TRUNC(order_date) in a WHERE clause defeats a plain B-tree index on order_date, because the index stores the original values, not the truncated ones, so Oracle can't use it to seek and falls back to a full scan. Either rewrite the predicate as a range (order_date >= TO_DATE(...) AND order_date < TO_DATE(...) + 1) so the plain index is usable, or create a function-based index that matches the expression: CREATE INDEX idx_orders_trunc_date ON orders (TRUNC(order_date));
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free