Converting mm/dd/yyyy date column string to yyyy-mm-dd in Databricks SQL

Question

I am trying to convert a date column which is in string type to date type in yyyy-mm-dd format but I am getting null as output in databricks SQL.
I tried date(CAST(date_col as timestamp)) also I tried date(date_col) both are giving null
sample Column

Output required

Answer 1

You seem to be looking for to_date() in databricks sql, which turns a string to a date:

select t.*,
    to_date(date_col, 'M/d/yyyy') new_date_col
from mytable t

Consider adapting the format specifier for your actual dataset; this assumes strings like '4/26/2023' or '4/1/2023'.