TO_DATE

TO_DATE(string_expr, format) converts a string expression into a date.

Syntax and Parameters

TO_DATE(string_expr, format)

• string_expr - (required): The string or expression you want to convert to a date.
• format - (optional):
  • Specifies the expected format of the input string.
  • If omitted:
    • On Snowflake: Attempts to auto-detect using the DATE_INPUT_FORMAT session parameter.
    • On Databricks: Defaults to Spark's CAST(expr AS DATE) behavior, which may return NULL for invalid formats.

Common Format Elements

These format strings can vary slightly by data platform.

Format	Description	Example
yyyy	Four-digit year	2025
MM	Two-digit month (01–12)	05
dd	Two-digit day of month	06
HH	Hour in 24-hour format	14
mm	Minutes	30
ss	Seconds	59

Basic Examples

TO_DATE('2025-05-06', 'yyyy-MM-dd') -- Returns: 2025-05-06
TO_DATE('06/05/2025', 'dd/MM/yyyy') -- Returns: 2025-05-06

Default Behavior

When no format is specified:

TO_DATE('2025-05-06')

• Snowflake: Tries to infer the format.
• Databricks: Casts string to date; may return NULL for unrecognized formats.

Handling Invalid Dates

When parsing date strings, it’s common to encounter values that don’t match the expected format—such as typos, unexpected separators, or completely invalid input (like 'April 35'). How these errors are handled differs by data platform.

Snowflake

In Snowflake, if the input string does not match the expected format, the standard TO_DATE() function throws an error:

TO_DATE('invalid', 'yyyy-MM-dd')  -- Error: invalid input syntax for type date

To prevent your pipeline from breaking due to such errors, Snowflake provides a safe variant:

TRY_TO_DATE('invalid', 'yyyy-MM-dd')  -- Returns: NULL

TRY_TO_DATE() works just like TO_DATE(), but instead of throwing an error, it returns NULL if the input can't be parsed.

This is ideal in production transformations where you want to allow processing to continue even with malformed date strings.

Databricks

Databricks will throw an error if the data formate or value it encounters is invalid. It will stop the query. We recommend filtering out any invalid data before running TO_DATE().

Scenario	ANSI Mode ON	ANSI Mode OFF
Invalid date format or value	❌ Throws error (stops query)	✅ Returns NULL silently
Well-formed date string	✅ Converts to date	✅ Converts to date
Missing value or 'null' string	✅ Must be handled manually	✅ Must be handled manually

ANSI Mode

If you have ANSI mode off, then you can write a case statement to handle errors. Be sure to research and understand what changing ANSI mode means for your organization.

If it's off, you can write a case statement to handle incorrect values.

CASE
  WHEN {{SRC}} IS NULL OR LOWER({{SRC}}) = 'null' THEN NULL
  ELSE TO_DATE({{SRC}}, 'yyyy-MM-dd')
END

• ANSI compliance in Databricks Runtime
• ANSI mode in Databricks SQL

Using TO_DATE in Coalesce

When using TO_DATE in transforms or bulk editing, you can use Helper Tokens:

TO_DATE({{SRC}}, 'yyyy-MM-dd')

The {{SRC}} token automatically gets replaced with the fully qualified source Node and column name. For example:

• If source column is "CUSTOMER"."SIGNUP_DATE" it becomes TO_DATE("CUSTOMER"."SIGNUP_DATE", 'yyyy-MM-dd').

• If source column is "ORDERS"."O_ORDERDATE" it becomes TO_DATE("ORDERS"."O_ORDERDATE", 'yyyy-MM-dd').

• If the column is already a DATE or TIMESTAMP type, remove the format argument: TO_DATE({{SRC}})

---

What's Next?

• Snowflake conversion functions
• Databricks to_date function

---