Oracle TO_DATE in Snowflake, Databricks and BigQuery
Snowflake TO_DATE accepts Oracle-like format models. Databricks to_date uses Java patterns, and BigQuery uses PARSE_DATE with % elements. Oracle DATE also carries a time, while the targets' DATE does not.
Oracle function: TO_DATE(char [, fmt [, 'nlsparam']]) · Oracle docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | TO_DATE(string_expr [, 'format']) | Format elements such as YYYY, MM, DD, MON work as in Oracle. Returns a DATE without time; use TO_TIMESTAMP to keep the time. | Snowflake docs |
| Databricks | Same name | to_date(expr, 'yyyy-MM-dd') | Java-style patterns: 'YYYY-MM-DD' becomes 'yyyy-MM-dd'. Use try_to_date to get NULL instead of an error. | Databricks docs |
| BigQuery | Rewrite | PARSE_DATE('%Y-%m-%d', string) | Use PARSE_DATETIME to keep the time. SAFE.PARSE_DATE returns NULL on bad input. | BigQuery docs |
Behavior differences to check
- TO_DATE('2025-03-01 14:30', 'YYYY-MM-DD HH24:MI') keeps 14:30 in Oracle. The targets drop the time when parsing to DATE, so parse to a timestamp type when the time matters.
Databricks
- Copying the Oracle model unchanged ('YYYY-MM-DD') parses week-based years and day-of-year in Java patterns and gives wrong dates; always use lower-case yyyy and dd.
Examples
Parse dates from a staging column
SELECT TO_DATE(order_day, 'YYYY-MM-DD') AS order_date FROM staging;SELECT TO_DATE(order_day, 'YYYY-MM-DD') AS order_date FROM staging;SELECT to_date(order_day, 'yyyy-MM-dd') AS order_date FROM staging;SELECT PARSE_DATE('%Y-%m-%d', order_day) AS order_date FROM staging;