Oracle TRUNC (date) in Snowflake, Databricks and BigQuery
Oracle TRUNC(date) without a unit removes the time of day. Snowflake keeps TRUNC for dates, Databricks has trunc and date_trunc, and BigQuery uses DATE_TRUNC. Pass the unit explicitly in every target.
Oracle function: TRUNC(date [, fmt]) · Oracle docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | TRUNC(date_or_time_expr, date_or_time_part) | Same as DATE_TRUNC with the arguments swapped. Units are date parts such as 'MONTH' or 'MM'. | Snowflake docs |
| Databricks | Same name | trunc(expr, unit) | Returns a DATE. Use date_trunc(unit, expr) to keep a TIMESTAMP. | Databricks docs |
| BigQuery | Rewrite | DATE_TRUNC(date_expression, MONTH) | Use DATETIME_TRUNC or TIMESTAMP_TRUNC for other types. | BigQuery docs |
Behavior differences to check
- TRUNC(d) with no unit means 'DD' in Oracle. In the targets, write CAST(d AS DATE) or pass 'DAY'.
- Oracle format models such as 'IW' (ISO week) and 'Q' map to the target's WEEK or ISOWEEK and QUARTER parts; check week starts.
Examples
Revenue by month
SELECT TRUNC(order_date, 'MM') AS month, SUM(amount) FROM orders GROUP BY TRUNC(order_date, 'MM');SELECT TRUNC(order_date, 'MM') AS month, SUM(amount) FROM orders GROUP BY 1;SELECT trunc(order_date, 'MM') AS month, SUM(amount) FROM orders GROUP BY 1;SELECT DATE_TRUNC(order_date, MONTH) AS month, SUM(amount) FROM orders GROUP BY 1;