Need someone to write SQL query for converting multiple formats of date from string format to date format.

I have a table in bigquery where under name and column (both string datatype) are having name =date_received and value under it is a receipt date.
So we need to fetch value wherever name = date_received and then convert all date formats to mm/dd/yyyy format.