to_date function

Applies to: Databricks SQL Databricks Runtime

Returns expr cast to a date using an optional formatting.

Syntax

to_date(expr [, fmt] )

Arguments

• expr: A STRING expression representing a date.
• fmt: An optional format STRING expression.

Returns

A DATE.

If fmt is supplied, it must conform with Datetime patterns.

If fmt is not supplied, the function is a synonym for cast(expr AS DATE).

If fmt is malformed or its application does not result in a well formed date, Azure Databricks raises an error.

Common error conditions

• CAST_INVALID_INPUT
• CANNOT_PARSE_TIMESTAMP

Note

In Databricks Runtime, if spark.sql.ansi.enabled is false, the function returns NULL instead of an error for malformed dates.

Examples

> SELECT to_date('2009-07-30 04:17:52');
 2009-07-30
> SELECT to_date('2016-12-31', 'yyyy-MM-dd');
 2016-12-31

> SELECT to_date('not-a-date');
  Error: CAST_INVALID_INPUT

Related functions

• cast function
• date function
• to_timestamp function
• Datetime patterns