Multiple Date Formats

[dunkindonut7777]

Hi I have a data here that has a date column with different format. I want to convert it in the same format. See sample table below:

DATE	(Expected output) DATE
4/25/2023	4/25/2023
17/10/2023	10/17/2023
12/3/2022	3/12/2023

How can I convert this?

[Peachyco]

Interesting. This looks very tricky. How can you tell whether the order is already correct, or if it needs fixing?

It's easy when the date is already past the 12th - there are only 12 months so you can easily tell which one is the month and which one is the date. But when the date is 12 or lower?

How did you determine that the last record ("12/3/2022") needed to be reformatted?

[caltang]

Is there a way to identify the data being MM/DD/YYYY or DD/MM/YYYY? I don't think this has an easy way to determine!

[dunkindonut7777]

this is instructed by the client

[Peachyco]

@dunkindonut7777 The client just told you that the last record "12/3/2022" needed to switched? Then it's the client who holds the logic for this switching. You need to clarify with them and understand what rules they use for the switching. They might be considering other columns or factors that we can't see from this one column of dates.

[caltang]

It is your job to ask the client my friend! @dunkindonut7777 

[dunkindonut7777]

I already asked and according to them, they inputted different formats on dates. Some of it were inputted as dd/mm/yyyy and some of it were inputted as mm/dd/yyyy. That is why I want to fix this with a single and uniform date format. :)

[caltang]

Not going to lie, this is almost impossible to discern between records if there is no standard approach. We can only assume as such... accuracy will be compromised. 

[Peachyco]

Hmm, then I don't think we can properly format all of it. Right off the bat, the only thing I can think of is fixing those dates that are past the 12th because, in those cases, I am sure of the month and the date segments. When we encounter dates like "12/3" or "9/11", we can't tell which is the date and the month.