How to convert multiple date formats in same field?

[mistydennis]

I have some data coming in with multiple date formats in the same field, and I'm having trouble reporting on these dates. I'd like to keep the dates consistent - how do I create a statement to change just the dates that are in the undesirable format?

What I have:

DateAdded

2021-11-03

2/15/2022

1/13/2023

What I would like:

DateAdded

2021-11-03

2022-02-15

2023-01-13

[bowesmana · Accepted solution]

There are plenty of ways to do this using various forms of eval. You can use if/case to match the types then convert based on match type as suggested, or you could do something like

| eval date=coalesce(strptime(DateAdded, "%m/%d/%Y"), strptime(DateAdded, "%F"))

which would parse each date/time until one of them converts to non-null

[Tom_Lundie]

Here is a one liner:

| eval DateAdded = if(like(DateAdded, "%/%/%"), strftime(strptime(DateAdded, "%m/%d/%Y"),"%Y-%m-%d"), DateAdded)

Based off your examples, I have assumed that the dates will either be in YYYY-mm-dd format or dd/mm/YYYY format.
If there are more than two formats, you might want to break these out into multiple fields using a rex command.