to_date function

Applies to: check marked yes Databricks SQL check marked yes 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.

To return NULL instead of an error when expr is malformed, use try_cast(expr AS DATE) or, in Databricks Runtime, set spark.sql.ansi.enabled to false.

Common error causes and resolutions

to_date raises an error when:

  • The input string doesn't match the expected format: When fmt is not specified, the input must be in yyyy-MM-dd or yyyy-MM-dd HH:mm:ss format. If your data uses a different format, such as dd/MM/yyyy or MM-dd-yyyy, specify the matching fmt argument. See Datetime patterns for all supported pattern letters.
  • The input string contains invalid date values: For example, month 13, day 32, or non-numeric characters where digits are expected.
  • The format string uses unsupported pattern letters: See Datetime patterns for supported symbols and formatting rules.

Error conditions

  • CAST_INVALID_INPUT: Raised when no fmt is provided and the input string cannot be parsed as a date.
  • CANNOT_PARSE_TIMESTAMP: Raised when fmt is provided but the input string does not match the given format pattern.

Note

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

Examples

-- Parsing with default format (yyyy-MM-dd)
> SELECT to_date('2009-07-30 04:17:52');
 2009-07-30

-- Parsing with an explicit format
> SELECT to_date('2016-12-31', 'yyyy-MM-dd');
 2016-12-31

-- Parsing a non-default date format
> SELECT to_date('31/12/2016', 'dd/MM/yyyy');
 2016-12-31

-- Error: input is not a valid date string
> SELECT to_date('not-a-date');
  Error: CAST_INVALID_INPUT

-- Error: input format doesn't match the default yyyy-MM-dd format
> SELECT to_date('31/12/2016');
  Error: CAST_INVALID_INPUT

-- Fix: supply a fmt that matches the input format
> SELECT to_date('31/12/2016', 'dd/MM/yyyy');
  2016-12-31

-- Use try_cast to return NULL instead of an error for invalid input
> SELECT try_cast('not-a-date' AS DATE);
  NULL
  • Last updated on