Skip to main content

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.
      • On BigQuery: Uses PARSE_DATE or DATE casting depending on input type. Specify a format string when the source is not ISO YYYY-MM-DD.

Common Format Elements​

These format strings can vary slightly by data platform.

FormatDescriptionExample
yyyyFour-digit year2025
MMTwo-digit month (01-12)05
ddTwo-digit day of month06
HHHour in 24-hour format14
mmMinutes30
ssSeconds59

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')

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. It is available on Snowflake only.

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

BigQuery​

Use PARSE_DATE with an explicit format string:

PARSE_DATE('%Y-%m-%d', '2025-05-06')

For safe parsing that returns NULL on failure instead of raising an error:

SAFE.PARSE_DATE('%Y-%m-%d', 'invalid')  -- Returns: NULL

BigQuery format elements use %Y, %m, and %d rather than Snowflake's yyyy and MM tokens. Test format strings against sample values in your warehouse.

Databricks​

Databricks throws an error if the date format or value is invalid and stops the query. Filter out invalid data before running TO_DATE().

ScenarioANSI Mode ONANSI Mode OFF
Invalid date format or valueThrows error and stops the queryReturns NULL silently
Well-formed date stringConverts to dateConverts to date
Missing value or 'null' stringMust be handled manuallyMust 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.

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

Using TO_DATE in Coalesce​

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

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

Expanded:

TO_DATE("CUSTOMER"."SIGNUP_DATE", 'yyyy-MM-dd')

If the column is already a DATE or TIMESTAMP type, remove the format argument on Snowflake and Databricks: TO_DATE({{SRC}}). On BigQuery, cast directly: DATE({{SRC}}).

What's Next?​