Convert date format 'MM/DD/YYYY' to 'DD/MM/YYYY'

[PepeMontes]

Hello Dear experts,
I am facing an issue with some dates into my data set, i have many dates with the next format 'DD / MM / YYYY' but also i have some with this format 'MM / DD / YYYY' so these dates are not valid to my date format.
it's possible to convert those dates to 'DD / MM / YYYY' format? Like: If into the date field the Month position it's greatern than 12 change the from 'MM / DD / YYYY' to 'DD / MM / YYYYY'?

I hope someone already dealed with something like this.

Regards.

[AdiPai]

Hi ,
Use Date(Date#('Date_Feild','Existing format '),'New Format') AS Date_Feild in the script function .

[PepeMontes]

Hello AdiPai,
These only change the format of my date field, i want to keep my actual date format, just want to convert the dates with different format(MM/DD/YYYY) to my actual format(DD/MM/YYYY).


Regards.

[sonkumamon]

What you are asking is basically impossible, since you don't have any way to recognize the date format for dates with 12 or less in the DD part. For example, you cannot define if 03/04/2020 is DD/MM/YYYY or MM/DD/YYYY.

[AdiPai]

it would be impossible to say what 01/01/2020 means ..  Why is the date in same feild in 2 formats ? are they from different sources - if yes ,and we know if source 1 = MM/DD/YYYY and Source 2 = DD/MM/YYYY we can seperate the data into 2 feilds - convert them back to a single format and then join these feilds together .