Converting date from string to date in sparksql

Question

I have a sparksql dateframe with dates in the following format: "26MAR2015". In following question they use the to_date function with java simpledataformat: Convert date from String to Date format in Dataframes to convert the strings in date. I could not find more information about this format in following question: Convert pyspark string to date format.

I do not find the correct format for my case:

spark.sql("""
SELECT TO_DATE(CAST(UNIX_TIMESTAMP('15MAR2015', '??????') AS TIMESTAMP)) AS newdate"""
).show()

Answer 1

You should use ddMMMyyyy as the format string.

For example, you could do:

spark.sql("""
SELECT CAST(FROM_UNIXTIME(UNIX_TIMESTAMP('15MAR2015', 'ddMMMyyyy')) AS date) AS newdate"""
).show()
#+----------+
#|   newdate|
#+----------+
#|2015-03-15|
#+----------+

You can find more information about the Java SimpleDateFormat here.