Databricks SQL throws PARSE_DATETIME_BY_NEW_PARSER

Question

I have a column in my databricks table, with a customised date time format as string, while trying to convert the string to datetime I am observing below error PARSE_DATETIME_BY_NEW_PARSER

SQL Command

select to_date(ORDERDATE, 'M/dd/yyyy H:mm') from sales_kaggle_chart limit 10;

The format of ORDERDATE column is M/dd/yyyy H:mm

example of ORDERDATE columns 10/10/2003 0:00 and 8/25/2003 0:00

complete error message

Job aborted due to stage failure: [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 '5/7/2003' 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.

Note: the same command works for a single value

SELECT to_date("12/24/2003 0:00", 'M/d/yyyy H:mm') as date;

You should try with 'M/d/yyyy H:mm' and it should work. It would throw an error even if any of the date value us less than 10 and you represent it without 0 prefix if you use dd in the format. — Nikunj Kakadiya
– Nikunj Kakadiya, Commented Dec 31, 2022 at 6:45
Thanks Nikunj Kakadiya, the solution worked, the part where i got confused the error message , thanks for the infro. — Bharathan Kumaran
– Bharathan Kumaran, Commented Jan 5, 2023 at 12:47

Bartosz Gajda · Accepted Answer · 2023-01-02 18:43:32Z

1

Have you tried setting to legacy parser, like the error message is hinting you?

SET legacy_time_parser_policy = legacy;
SELECT to_date(ORDERDATE, 'M/dd/yyyy H:mm') FROM sales_kaggle_chart LIMIT 10;

This error is quite common, and adjusting configuration typically does the job.

answered Jan 2, 2023 at 18:43

Bartosz Gajda

1,1877 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bharathan Kumaran Over a year ago

Thanks Nikunj Kakadiya mentioned and I have able to get the sql work.

Collectives™ on Stack Overflow

Databricks SQL throws PARSE_DATETIME_BY_NEW_PARSER

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related