Convert dd-mmm-yyyy to yyyy-mm-dd in sparksql

Question

I have a date value in this format 'dd-mmm-yyyy'(Example31-Mar-2020) in a glue table. I need to transform this to 'yyyy-mm-dd' (output:2020-03-31) format using sparkSql.

I have tried. "date_format(reference_line_attribute3, 'yyyy-mm-dd')" but this just gives null as output.

Please help. Thank you

Answer 1

This should do the trick

df.withColumn("newDate", 
   date_format(
               to_date($"reference_line_attribute3", "dd-MMM-yyyy"),
               "yyyy-MM-dd"))

Output

+-------------------------+----------+
|reference_line_attribute3|   newDate|
+-------------------------+----------+
|              31-Mar-2020|2020-03-31|
+-------------------------+----------+