Inconsistent Date format (string)

I have a field that contains date values, currently a string field, that has multiple formats that i need to convert to an actual date field. The current values are "yyyy-mm-dd" and "mm/dd/yyy". I have tried the DateTime parse tool but with multiple formats this isnt successful. Anyone run across this?

Date Time

Accepted answers

MarqueeCrew

@jdunkerley79

beat me to the punch, but here is what I did differently:

IF
Contains([date],"-") THEN date
ELSEIF
Contains([date],"/") THEN Regex_Replace([date],"(\d{1,2})(/)(\d{1,2})(/)(\d{4})","$5-$1-$3")
ELSE
Null()
ENDIF

I setup a conditional set of logic to handle and look at the data (this includes a default of Null). You could change the default date if you want and if you find that you have another format of data, you can create another ELSEIF statement.

I didn't know that I could not use the (/) group on the delimiter. Knowing that, I might use a \W instead. It is a non-word character to look for any delimiter.

Cheers,

Mark

All comments

JohnJPS

I would recommend just something like....

IIF(Contains([f1],'-'),[f1],
    DateTimeParse([f1],"%m/$d/%Y"))

jdunkerley79

yep - was one of the reasons I build a custom tool: https://github.com/jdunkerley/AlteryxAddIns/releases/tag/v0.3

Easiest work around is a formula tool:

REGEX_Replace([Field1],"(\d\d)/(\d\d)/(\d{4})","$3-$1-$2")

This will convert mm/dd/yyyy to yyyy-mm-dd

There is no need to parse yyyy-mm-dd as Alteryx knows it as a date anyway

MarqueeCrew

@jdunkerley79

beat me to the punch, but here is what I did differently:

IF
Contains([date],"-") THEN date
ELSEIF
Contains([date],"/") THEN Regex_Replace([date],"(\d{1,2})(/)(\d{1,2})(/)(\d{4})","$5-$1-$3")
ELSE
Null()
ENDIF

I didn't know that I could not use the (/) group on the delimiter. Knowing that, I might use a \W instead. It is a non-word character to look for any delimiter.

Cheers,

Mark

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335