LEGACY_TIME_PARSER_POLICY

Applies to: check marked yes Databricks SQL

The LEGACY_TIME_PARSER_POLICY configuration parameter controls parsing and formatting of dates and timestamps as well as handling of dates prior to October, 15, 1582.

Databricks SQL uses the formatting defined in Datetime patterns to map datetime strings to datetime values. Once a datetime value is established it must be verified against a calendar.

You can set this parameter at the session level using the SET statement and at the global level using SQL configuration parameters or the SQL Warehouse API.

Settings

Three settings are supported:

  • LEGACY

    When LEGACY, is set extraneous fields in the string values that exceed the specified pattern are accepted. The calendar used is the hybrid Julian and Gregorian calendars.

  • CORRECTED

    When CORRECTED, the string value must be an exact match with the format string. The calendar used is the proleptic Gregorian calendar.

  • EXCEPTION

    When EXCEPTION, Databricks SQL raises an error whenever the result of LEGACY and CORRECTED are different.

System default

The system default for this parameter is EXCEPTION.

Examples 

> SET legacy_time_parser_policy = legacy;
> SELECT to_timestamp('2021-01-01 12:34:56', 'yyyy-MM-dd');
  2021-01-01 00:00:00

> SET legacy_time_parser_policy = corrected;
> SELECT to_timestamp('2021-01-01 12:34:56', 'yyyy-MM-dd');
  Text '2021-01-01 12:34:56' could not be parsed, unparsed text found at index 10