Standardize Multiple date formats of incoming data

[remarsha]

Hi Commnity,

Have an incoming source of data of which one of the columns is Date.

Issue: The format of the date is not consistent , mm/dd/yyyy or  dd/mm/yyyy or  yyyy/mm/dd in some cases.

For e.g., 

31/08/2022

2/6/2023

8/4/2019

The date along with the A/c no is used to form a unique identification filed for e.g.,  "MQTY" +" mm/dd/yyyyy" = "MQTY - 05/15/2023"

because the date format is inconsistent i am not getting the values required 

MQTY - 15/05/2023 instead of 05/15/2023

ZLOR- 04/08/2023 instead of 08/04/2023

KYFS - 2/6/2023 instead of 06/02/2023

How can i standardize the incoming date.

Note - I don't have or need a time in the date.

Thanks.

[PhilipMannering]

Can you do this?

if [A/C No] IN ('mqty', 'zlor', 'kyfs')
then datetimeparse([Date], '%d/%m/%Y')
else datetimeparse([Date], '%m/%d/%Y')
endif

[remarsha]

Thanks Philip.

The input A/c is running up to 20K and more records. so cannot do an if else based on A/C nos.

irrespective of the A/c nos, if the date format can be standardized it would help.