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.
