topic Pyspark to_date not coping with single digit Day or Month in Data Engineering

Pyspark to_date not coping with single digit Day or Month

RobDineen — Mon, 11 Nov 2024 11:31:48 GMT

Hi there i have a simple Pyspark To_date function but fails due to days or months from 1-9 so

is there a nice easy way to get round this at all

Regards

Rob

Re: Pyspark to_date not coping with single digit Day or Month

VZLA — Mon, 11 Nov 2024 11:52:10 GMT

Hi @RobDineen,

You may try setting the timeParserPolicy to meet your use case needs.

When LEGACY, java.text.SimpleDateFormat is used for formatting and parsing dates/timestamps in a locale-sensitive manner, which is the approach before Spark 3.0.

When set to CORRECTED, classes from java.time.* packages are used for the same purpose. The default value is EXCEPTION, RuntimeException is thrown when we will get different results.

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

spark.sql("set spark.sql.legacy.timeParserPolicy=LEGACY")

Re: Pyspark to_date not coping with single digit Day or Month

RobDineen — Mon, 11 Nov 2024 13:52:35 GMT

i have been trying to solve it with the following New column on the fly,

if DayofMonth in (1,2,3,4,5,6,7,8,9) then put a 0 before, else leave as is.

obviously I'm trying to insert the 0 incorrectly. but wondering how?

nearly there

Re: Pyspark to_date not coping with single digit Day or Month

RobDineen — Tue, 12 Nov 2024 09:53:22 GMT

Hi @VZLA

any idea with the below work around, I'm nearly there.

Re: Pyspark to_date not coping with single digit Day or Month

RobDineen — Wed, 13 Nov 2024 15:45:18 GMT

Resolved using format_string

dff = df.withColumn("DayofMonthFormatted", when(df.DayofMonth.isin([1,2,3,4,5,6,7,8,9]), format_string("0%d", df.DayofMonth)).otherwise(df.DayofMonth))