Instead of using SUBSTRING, pass the entire date-time field to the TO_DATE function with the MM-DD-YYYY format which encapsulates all 3 formats above.

Redshift has a strict mode that is turned off by default - TO_DATE(string, format, is_strict=false). When not in strict mode, the time will automatically be discarded & Redshift is clever enough to infer how to convert the single digits to double digits.

The rest of your code can then work as per normal.

To demonstrate:

Copy--MM-DD-YYYY H:MM:SS PM
SELECT TO_DATE('05-15-2022 4:12:34 PM','MM-DD-YYYY');
--M-DD-YYYY H:MM:SS PM
SELECT TO_DATE('4-01-2022 8:45:06 PM','MM-DD-YYYY');
--MM-D-YYYY H:MM:SS PM
SELECT TO_DATE('04-9-2022 6:23:10 PM','MM-DD-YYYY');

Output:

Copy2022-05-15
2022-04-01
2022-04-09

In your case, this should work:

CopyDENSE_RANK()
OVER (partition BY [columnA] ORDER BY 
TO_DATE(datecolumn, 'MM-DD-YYYY') DESC)

P.S. To begin with, these dates should all be in the same format.

If you are responsible for producing the data, please only output dates in one format moving forward to make everyone's lives' much easier!

Athena and Redshift have different SQL flavors (Redshift is based on PostgreSQL and Athena is based on Presto/Trino).

For Redshift use the TO_DATE function:

select to_date('20010630', 'YYYYMMDD');

Text Data Type in Redshift:

For Redshift Text Data Type you can use varchar(max) or varchar(65535)

That is the maximum length of a text data type field in Redshift.