Given String can be in format of any of the below :

• ‘5/8/2021 12:00:00 AM’ or
• ’05/8/2021 12:00:00 AM’ or
• ‘5/08/2021 12:00:00 AM’ or
• ’05/08/2021 12:00:00 AM’

Needed output = ‘2021-05-08’ in ‘YYYY-MM-DD’ format of Spark SQL. Any advise is appreciated. Tried like below:

SELECT
  CONCAT(
    SUBSTRING_INDEX('5/8/2021 12:00:00 AM', '/', -1),
    '-',
    LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX('5/8/2021 12:00:00 AM', '/', 1), '/', -1), 2, '0'),
    '-',
    LPAD(REGEXP_REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX('5/8/2021 12:00:00 AM', '/', 1), ' ', -1), '.* ', ''), 2, '0')
  ) AS converted_date;