[INCONSISTENT_BEHAVIOR_CROSS_VERSION.PARSE_DATETIME_BY_NEW_PARSER]

[117074]

Hi all,

I'm trying to join 2 views in SQL editor for some analysis. I get the following error:

[INCONSISTENT_BEHAVIOR_CROSS_VERSION.PARSE_DATETIME_BY_NEW_PARSER] You may get a different result due to the upgrading to Spark >= 3.0: Fail to parse '22/12/978' in the new parser. You can set "legacy_time_parser_policy" to "LEGACY" to restore the behavior before Spark 3.0, or set to "CORRECTED" and treat it as an invalid datetime string.

I've tried correcting this by using the command: 'SET legacy_time_parser_policy = legacy;' before my code but it still errors. 

Is anyone able to advise on how to solve this? 

Thanks

[Kaniz · Accepted solution]

Hi @117074 , Based on the error message you provided, it appears that Spark is unable to parse the date '22/12/978' due to changes in the datetime parser in Spark 3.0 and above.

As per the provided documentation, you have attempted to set the legacy_time_parser_policy to LEGACY but it still errors out. This is likely due to the fact that the date '22/12/978' is before the year 1582. From Spark 3.0, reading dates before 1582-10-15 can be ambiguous due to the calendar difference between Spark 3.0+'s Proleptic Gregorian calendar and Spark 2.x or legacy versions of Hive's hybrid calendar.

To address this issue, you can try to set the SQL config or the datasource option to "LEGACY" to rebase the datetime values with respect to the calendar difference during reading.

If you want to read the datetime values as it is, you can set the SQL config or the datasource option to "CORRECTED".

Please note that the exact command to set these options may vary depending on your specific use case and data source.

Here is a general example of how you might set these options in your code:

python
spark.conf.set("spark.sql.legacy.timeParserPolicy", "LEGACY")

Please replace "spark.sql.legacy.timeParserPolicy" with the appropriate SQL config or datasource option for your specific use case.

[117074]

Hi Kaniz, I found the equivalent SQL code for this but it didn't seem to store the operation past the execution. I.e I would run the code to configure settings, then run the troublesome code afterwards and still get the same result. 

The problem has been identified as source data missing a digit for date of birth year, and therefore having results before 1000AD. This has been rectified at source. 

Thank you for your time in answering!