Can't parse string to date format, keep getting null values

Question

I have a date column in string. Date is as follows: SinkCreatedOn = 7/30/2022 5:21:55 PM

I am trying to parse to datetype using following code but always get null values. I have tried many different specific date formats i.e M/d/y as it says in new spark documentation

df = df.withColumn("test", F.to_date(F.to_timestamp(col("SinkCreatedOn"), "MM/dd/yyyy HH:mm:ss a")))

test returns null

I tried this code on a column that only included date and it worked. When timestamp is present it breaks.

Any help much appreciated

Thank you

Picture of Issue

Azhar Khan · Accepted Answer · 2022-10-26 09:34:09Z

1

You are using HH which is for 24 Hr date format. Instead, use h. Please refer SimpleDateFormat JavaDoc

The following works:

df = spark.createDataFrame([
  ["07/30/2022 05:21:55 PM"],
], ["SinkCreatedOn"])


df.withColumn("test", F.to_date(F.to_timestamp(F.col("SinkCreatedOn"), "M/d/yyyy h:mm:ss a"))).show()

+----------------------+----------+
|SinkCreatedOn         |test      |
+----------------------+----------+
|07/30/2022 05:21:55 PM|2022-07-30|
+----------------------+----------+

edited Oct 26, 2022 at 9:34

answered Oct 25, 2022 at 12:03

Azhar Khan

4,15811 gold badges31 silver badges34 bronze badges

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

2 Comments

samkart Over a year ago

the padding isn't really reqd. 'M/d/yyyy h:mm:ss a' will work just fine.

Azhar Khan Over a year ago

You are right. I tested and modified the answer. Thanks!

Collectives™ on Stack Overflow

Can't parse string to date format, keep getting null values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related