LEGACY_TIME_PARSER_POLICY
Applies to: 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.
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 ofLEGACY
andCORRECTED
are different.
The system default for this parameter is EXCEPTION
.
> 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