Convert date from String to Date format in Dataframes

Question

I am trying to convert a column which is in String format to Date format using the to_date function but its returning Null values.

df.createOrReplaceTempView("incidents")

spark.sql("select Date from incidents").show()

+----------+
|      Date|
+----------+
|08/26/2019|
|08/26/2019|
|08/26/2019|
|06/14/2019|

spark.sql("select to_date(Date) from incidents").show()

+---------------------------+
|to_date(CAST(Date AS DATE))|
 +---------------------------+
|                       null|
|                       null|
|                       null|
|                       null|

The Date column is in String format:

 |-- Date: string (nullable = true)

Answer 1

I discovered a simple approach to solve your problem(works with "YY--MM--DD" format):

Another Method:

(Works in Spark 2.3+) Use TO_DATE(CAST(UNIX_TIMESTAMP(date, 'MM/dd/yyyy') AS TIMESTAMP))

val df1=spark.sql(""" | SELECT TO_DATE(CAST(UNIX_TIMESTAMP('09/11/2019', 'MM/dd/yyyy') AS TIMESTAMP)) AS finaldate""" | ).show() +----------+ | finaldate| +----------+ |2019-07-11| +----------+