Convert string date to date format in pyspark SQL

user_11947796 0
Viewed 559

I have a date column in table in which is in string format I need to convert this string date into date type format

This is what my date column looks like

+----------+
|      date|
+----------+
|2018_07   |
+----------+

I need to convert this date in this desired format in date format and not in string format

+----------+
|      Date|
+----------+
|2018-07-01|
+----------+

I am trying to use this but its giving me null values under date column

%sql
SELECT Col1,Col2,Col3,Col4, 
TO_DATE(
      CAST(
        UNIX_TIMESTAMP(date, 'yyyy-MM-01') AS TIMESTAMP
      )
    ) as Date 
,sales
FROM db.table

Any kind of help is appreciated

databricks apache-spark-sql
1 Answers

I simplified your code now it will work fine (just tested it) :-)

%sql
SELECT 
  Col1,
  Col2,
  Col3,
  Col4,
  TO_DATE(TRIM(date), 'yyyy_MM') AS DATE,
  sales
FROM 
  db.table
user_109729590
Related