Trying to convert a string to a date column in databricks SQL

have a table with information that's mostly consisted of string columns, one column has the date listed in the 103 format (dd-mm-yyyy) as a string, would like to convert it to a date column in databricks sql, but I can't find a conventional method to do so. I don't mind if the answer lies in it being converted to yyyy-mm-dd. Just wondering how to do it, I've tried alter column and even the pyspark with column method, but it doesn't seem to work at all. Any help is appreciated thanks.

Edit: I'd like to alter the column in the table permanently, not as a select query.

I've tried to use alter column with this result:

ALTER TABLE table_name
ALTER COLUMN colname TYPE to_date(colname,'dd/mm/yyyy')

Error in SQL statement: ParseException: 
mismatched input 'colname' expecting INTEGER_VALUE(line 2, pos 37)

== SQL ==
ALTER TABLE tablename
ALTER COLUMN colname TYPE to_date(colname,'dd/mm/yyyy')
                                  

As well as

%python
from pyspark.sql.functions import col


(spark.read.table("tablename")
  .withColumn("colname", col("colname").cast('date'))
  .write
  .mode("overwrite")
  .option("overwriteSchema", "true")
  .saveAsTable("tablename")
)

Which returns me null values

You use like below in pyspark.

df2.withColumn("Date_w_type", to_date("date","dd/MM/yyyy")).show()

In sql,

%sql

ALTER  TABLE tbl_tmp ADD  COLUMN new_date_column DATE;

UPDATE tbl_tmp
SET new_date_column =  CAST(TO_DATE(curr_date, 'dd/MM/yyyy') AS  DATE);

SELECT  *  from tbl_tmp;