How to extract the year and quarter from the String date in databricks SQL

Question

Can someone show me how to extract the year from the String date in databricks SQL.

I am based in the UK and our date format is normally as follows:

dd/mm/yyyy

The field containing the dates is set as StringType()

I am trying to extract the year from the string as follows:

select year(cast(financials_0_accountsDate as Date)) from `financiallimited_csv`

I'm using the following the code to extract the quarter

select quarter(cast(financials_0_accountsDate as Date)) from `financiallimited_csv`

However, both result in NULL values.

Any thoughts on how to extract the year and quarter from dates with StringType() dd/mm/yyyy?

The table looks like the following:

Answer 1

Could you try the to_date function?

select year(to_date(financials_0_accountsDate, 'dd/MM/yyyy')) from `financiallimited_csv`