Oracle TO_CHAR in Snowflake, Databricks and BigQuery
Snowflake TO_CHAR understands Oracle-style format models such as 'YYYY-MM-DD HH24:MI:SS'. Databricks to_char works on dates from Runtime 14.1 but uses Java-style patterns, and BigQuery uses FORMAT_DATE with % elements.
Oracle function: TO_CHAR(datetime [, fmt [, 'nlsparam']]) · Oracle docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | TO_CHAR(expr [, 'format']) | Format elements are close to Oracle: YYYY, MM, DD, HH24, MI, SS, MON, DY. | Snowflake docs |
| Databricks | Rewrite | date_format(expr, 'yyyy-MM-dd HH:mm:ss') | Java-style patterns: 'YYYY-MM-DD HH24:MI:SS' becomes 'yyyy-MM-dd HH:mm:ss'. to_char also accepts dates in Databricks Runtime 14.1 and above, with the same patterns. | Databricks docs |
| BigQuery | Rewrite | FORMAT_DATE('%Y-%m-%d', date) / FORMAT_DATETIME('%Y-%m-%d %H:%M:%S', dt) | Use the FORMAT_* function that matches the input type. | BigQuery docs |
Behavior differences to check
- Oracle fill mode (FM) and NLS parameters for month names have no exact equivalent; check any format that uses FM, MONTH or DAY with known dates.
- TO_CHAR on numbers with format models such as '999,999.00' also differs per target; test it separately from date formats.
Databricks
- Oracle MI (minutes) and MM (month) map to mm and MM in Databricks. Copying the Oracle model unchanged produces wrong output instead of an error in many cases.
Examples
Timestamp as text
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') AS created_text FROM events;SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') AS created_text FROM events;SELECT date_format(created_at, 'yyyy-MM-dd HH:mm:ss') AS created_text FROM events;SELECT FORMAT_DATETIME('%Y-%m-%d %H:%M:%S', created_at) AS created_text FROM events;