-- Create Alteryx input (one column with strings)
WITH input AS
(
SELECT '29-01-2020,dd.mm.yyyy' AS date_field
UNION
SELECT '03/26/1920,mm/dd/yyyy' AS date_field
UNION
SELECT '11/16/2008,mm-dd-yyyy' AS date_field
UNION
SELECT '15/12/2005,dd/mm/yyyy' AS date_field
UNION
SELECT '1996-15-03,yyyy-dd-mm' AS date_field
)
-- Separate date_field to two columns date_string and date_format
,
input_columns_split AS
(
SELECT 
date_field
,LEFT(date_field,PATINDEX('%,%',date_field)-1) AS date_string
,RIGHT(date_field,PATINDEX('%,%',date_field)-1) AS date_format
FROM input
)
-- Apply IF condition (only for 3 date types) and convert date_string to date_formatted as date
SELECT *
,CAST(
	CASE 
		WHEN LEFT(date_format,1) = 'd' THEN CONCAT(RIGHT(date_string,4),'-',SUBSTRING(date_string,4,2),'-',LEFT(date_string,2))
		WHEN LEFT(date_format,1) = 'm' THEN CONCAT(RIGHT(date_string,4),'-',LEFT(date_string,2),'-',SUBSTRING(date_string,4,2))
		ELSE CONCAT(LEFT(date_string,4),'-',RIGHT(date_string,2),'-',SUBSTRING(date_string,6,2)) END
AS date) AS date_formatted
FROM input_columns_split
ORDER BY date_formatted