I imported a large csv file into databricks as a table.

I am able to run sql queries on it in a databricks notebook.

In my table, I have a column that contains date information in the mm/dd/yyyy format :

12/29/2015

12/30/2015 etc...

Databricks imported this column with type str, instead of date. Forcing a 'timestamp' type in the Table UI did not have any effect.

How can I convert this column type to a date inside sql?

I tried to do

select cast(arrival_date as date) from my_data_table

however, this requires that the str column is in YYYY-mm-dd format. And mine is mm/dd/yyyy format as mentioned above.

select to_date('15/1/09') as date;

does not work either for the same reason.

What can I do to have a column of dates?

Is it true that SparkSQL does not support 'update' operations? In that case, I cannot rearrange the string to fit the format either? What options do I have?