How do I prioritize date format 'MM/DD/YYYY' over 'MM-DD-YYYY'

Question

How can I write a query in Teradata so that if the field Cont_Num has duplicate with different data formats('MM/DD/YYYY' and 'MM-DD-YYYY'), the query should only return the field with date format 'MM/DD/YYYY', but if there are no duplicates for Cont_Num and the date format present is only 'MM-DD-YYYY', it should return that field Example of the data i have is below

SELECT Cont_Num,FILL_DATE, 
FROM USS_LOAD_LOG_TABLES.Members 
WHERE Cont_Num IN ( 
1807369967,
1807453269,
1916796684,
2015276712,
2001306287
)
ORDER BY Cont_Num,FILL_DATE;

RESULT

Cont_Num        FILL_DATE
1807369967  11-13-2018
1807369967  11/13/2018
1807453269  11-18-2018
1807453269  11/18/2018
1916796684  06-11-2019
1916796684  6/11/2019
2001306287  01-08-2020
2001306287  1/8/2020
2015276712  03-28-2020

EXPECTED RESULT

RESULT

Cont_Num        FILL_DATE
1807369967  11/13/2018   
1807453269  11/18/2018
1916796684  6/11/2019
2001306287  1/8/2020
2015276712  03-28-2020

Answer 1

Of course, you should try to fix your data model, a date should be stored as DATE.

You can add a leading zero to a single digit day/month and remove the hyphen/dash to normalize the strings to a common format, which can be used in a ROW_NUMBER:

qualify
   row_number()
   over (partition by Cont_Num, RegExp_Replace(RegExp_Replace(FILL_DATE, '\b([\d])\b', '0\1'), '\D')
         order by case when FILL_DATE like '%/%' then 1 else 2 end) = 1

Hopefully only MMDDYYYY format is used.