How to TRUNCATE DATE in Snowflake

How do I truncate a date to month or year in Snowflake?

DATE_TRUNC (or TRUNC) rounds a date or timestamp down to the specified date part (year, month, week, day, etc.).

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does DATE_TRUNC do in Snowflake?

DATE_TRUNC returns the first instant of the requested date part, letting you group or compare data at consistent boundaries such as month-start or week-start.

How do I write the DATE_TRUNC syntax?

Use either DATE_TRUNC('part', date) or TRUNC(date, 'part'). Both are interchangeable for dates.

Which date parts can I truncate to?

Common parts: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND. Snowflake ignores case.

When should I truncate dates?

Aggregate metrics per period, join tables on aligned dates, or create calendar dimensions.Example: total monthly revenue.

Example – monthly revenue by customer

SELECT c.id, DATE_TRUNC('month', o.order_date) AS order_month, SUM(o.total_amount) AS monthly_spend FROM Orders o JOIN Customers c ON c.id = o.customer_id GROUP BY c.id, order_month ORDER BY order_month;

Best practices for DATE_TRUNC

Prefer explicit date parts

Write DATE_TRUNC('month',23-08-15), not shorthand numbers, for readability.

Keep columns typed

Store dates in DATE or TIMESTAMP columns so DATE_TRUNC can use metadata and avoid casts.

Common mistakes and fixes

Using unquoted date parts

Wrong: DATE_TRUNC(month, order_date).Right: DATE_TRUNC('month', order_date).

Truncating timestamps without preserving timezone

DATE_TRUNC drops timezone info. Cast to TIMESTAMP_LTZ first if you need local time.

Related functions

DATE_PART extracts a specific part; TO_DATE converts strings to dates; DATEADD shifts dates.

Why How to TRUNCATE DATE in Snowflake is important

How to TRUNCATE DATE in Snowflake Example Usage


-- Weekly active customers created in the last 90 days
SELECT DATE_TRUNC('week', created_at) AS signup_week,
       COUNT(*) AS new_customers
FROM   Customers
WHERE  created_at >= DATEADD(day, -90, CURRENT_DATE)
GROUP  BY signup_week
ORDER  BY signup_week;

How to TRUNCATE DATE in Snowflake Syntax


-- Primary form
DATE_TRUNC('date_part', <date_or_timestamp_expr>)

-- Alternate form
TRUNC(<date_expr>, 'date_part')

-- date_part options
YEAR | QUARTER | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND

Common Mistakes

Forgetting to quote the date part (e.g., DATE_TRUNC(month, order_date)). Snowflake treats unquoted identifiers as columns, causing an “unrecognized name” error. Always wrap parts in single quotes: DATE_TRUNC('month', order_date).
Assuming DATE_TRUNC retains timezone information. It does not; the result is TIMESTAMP_NTZ. Cast to TIMESTAMP_LTZ before truncation if local time matters: DATE_TRUNC('hour', order_time::TIMESTAMP_LTZ).

Frequently Asked Questions (FAQs)

Can I truncate to the beginning of a fiscal year?

Yes. If your fiscal year starts in July, add or subtract months before truncation: DATEADD(month, 6, DATE_TRUNC('year', DATEADD(month, -6, order_date))).