Pyspark to_date not coping with single digit Day or Month

RobDineen
Contributor

‎11-11-2024 03:31 AM

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

RobDineen_0-1731324661487.png

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

Regards

Rob

0 Kudos

VZLA
Databricks Employee
Databricks Employee

‎11-11-2024 03:52 AM

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") 

or

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

0 Kudos

RobDineen
Contributor
In response to VZLA

‎11-11-2024 05:47 AM - edited ‎11-11-2024 05:52 AM

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.

RobDineen_0-1731332791231.png


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

 

 

nearly there

 

RobDineen_1-1731333144746.png

 

0 Kudos

RobDineen
Contributor
In response to VZLA

‎11-12-2024 01:53 AM

Hi @VZLA 

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

0 Kudos

RobDineen
Contributor

‎11-13-2024 07:45 AM

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))

View solution in original post

0 Kudos