Dates are parsed by ignoring trailing invalid characters

Learn about the change in the way dates are parsed from a string by ignoring trailing invalid characters

Before upgrade to CDP 7.1.7 SP2 Cumulative hotfix 14

HIVE-20007 introduced changes in the way dates were parsed from strings. SQL functions or date operations involving invalid dates returned "null".

After upgrade to CDP 7.1.7 SP2 Cumulative hotfix 14

HIVE-27586 extracts and returns a valid date from a string value if there is a valid date prefix in the string. This fix partially restores the behavior changes introduced as part of HIVE-20007 and also makes the current behavior of handling trailing invalid characters more consistent.

The following table illustrates the behavior changes before and after the fix:

String value Behavior (before HIVE-20007) Previous behavior (after HIVE-20007) Current behavior (after HIVE-27586)
2023-08-03_16:02:00 2023-08-03 null 2023-08-03
2023-08-03-16:02:00 2023-08-03 null 2023-08-03
2023-08-0316:02:00 2024-06-11 null 2023-08-03
03-08-2023 0009-02-12 null 0003-08-20
2023-08-03 GARBAGE 2023-08-03 2023-08-03 2023-08-03
2023-08-03TGARBAGE 2023-08-03 2023-08-03 2023-08-03
2023-08-03_GARBAGE 2023-08-03 null 2023-08-03

This change affects various Hive SQL functions and operators that accept dates from string values, such as CAST (V AS DATE), CAST (V AS TIMESTAMP), TO_DATE, DATE_ADD, DATE_DIFF, WEEKOFYEAR, DAYOFWEEK, and TRUNC.