How to TRUNCATE Date in Oracle

Galaxy Glossary

How do I truncate a DATE or TIMESTAMP in Oracle?

Oracle’s TRUNC(date) function removes the time portion or higher-level date parts (month, year, etc.) from a DATE or TIMESTAMP value.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

What does TRUNC(date) do in Oracle?

TRUNC(date) strips unwanted time or higher-level date parts, returning the date portion you specify. It simplifies grouping, filtering, and comparison by normalizing timestamps to a common boundary.

How do I truncate a timestamp to midnight?

Call TRUNC(order_date). Oracle drops the hours, minutes, and seconds, yielding the date at 00:00:00. Use this when grouping daily sales from the Orders table.

How can I get the first day of the month?

Provide a format mask: TRUNC(order_date, 'MM').This resets day and time, helpful for monthly revenue summaries.

How do I truncate to a year or quarter?

Use format masks 'YYYY' or 'Q'. Example: TRUNC(order_date, 'YYYY') for fiscal-year comparisons; TRUNC(order_date, 'Q') for quarterly KPIs.

Can I truncate to hours or minutes?

Yes. For hours, TRUNC(order_date, 'HH24'); for minutes, TRUNC(order_date, 'MI'). Useful when analyzing intraday traffic spikes.

When should I use TRUNC instead of ROUND?

TRUNC always floors to the boundary, while ROUND may roll forward.Prefer TRUNC when you must not cross the boundary—e.g., daily sales must stay inside the same day.

Best practices for TRUNC(date)

1) Index on truncated columns for performance. 2) Keep format masks in UPPERCASE. 3) Document why truncation is needed to avoid hidden time-zone issues.

Example: Daily revenue

Select TRUNC(order_date) AS sale_day, SUM(total_amount) FROM Orders GROUP BY TRUNC(order_date) ORDER BY sale_day;

Common mistakes and fixes

Using TRUNC in WHERE without function-based index: causes full scans; create index ON Orders (TRUNC(order_date)).
Forgetting second argument: TRUNC(order_date, 'MM') not TRUNC(order_date,'MON').The latter fails.

.

Why How to TRUNCATE Date in Oracle is important

How to TRUNCATE Date in Oracle Example Usage


-- First-of-month inventory snapshot
SELECT TRUNC(order_date,'MM')   AS month_start,
       SUM(oi.quantity * p.price) AS sales_value
FROM   Orders      o
JOIN   OrderItems  oi ON oi.order_id = o.id
JOIN   Products    p  ON p.id = oi.product_id
GROUP  BY TRUNC(order_date,'MM')
ORDER  BY month_start;

How to TRUNCATE Date in Oracle Syntax


TRUNC(<date_expr> [, <format_mask>])

<date_expr>   Any DATE or TIMESTAMP column, literal, or expression.
<format_mask> Optional string defining the level to truncate.

Common masks with ecommerce examples:
'HH24'  – Hour: SELECT TRUNC(created_at,'HH24') FROM Customers;
'MI'    – Minute: SELECT TRUNC(order_date,'MI') FROM Orders;
'DD'    – Day (default): SELECT TRUNC(order_date) FROM Orders;
'Q'     – Quarter: SELECT TRUNC(order_date,'Q') FROM Orders;
'YYYY'  – Year: SELECT TRUNC(order_date,'YYYY') FROM Orders;
'MM'    – Month: SELECT TRUNC(order_date,'MM') FROM Orders;

Common Mistakes

Function in WHERE without index: TRUNC(order_date) = TRUNC(SYSDATE). This forces a full table scan. Fix by creating a function-based index: CREATE INDEX idx_orders_trunc ON Orders(TRUNC(order_date)).
Wrong format mask: using 'MON' instead of 'MM'. 'MON' is for month names and causes ORA-018 Ve errors. Always use 'MM' for month truncation.

Frequently Asked Questions (FAQs)

Is TRUNC(date) deterministic?

Yes. Given identical inputs, it always returns the same output, making it safe for function-based indexing.

Does TRUNC change the original data?

No. It only alters the result in the query; the stored value remains intact.

How does TRUNC handle time zones?

TRUNC works on the stored session time zone. Convert to UTC first if you need cross-zone consistency.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.