Try using SAFE.PARSE_DATE. It will attempt to parse each row following the format you indicate. It will return null if there’s an error. Here’s an example:

SELECT FORMAT_DATE('%Y-%m-%d', COALESCE( SAFE.PARSE_DATE('%d-%b-%y', column), SAFE.PARSE_DATE('%Y-%m-%d', column) ) ) AS formatted FROM table;