0 votes
1 view
in Big Data Hadoop & Spark by (11.5k points)

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)

1 Answer

0 votes
by (25.6k points)

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

image

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

 

...