Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Inconsistent Date format (string)

doublej
7 - Meteor

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?

 

11 REPLIES 11
JohnJPS
15 - Aurora

I would recommend just something like....

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

 

jdunkerley79
ACE Emeritus
ACE Emeritus

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
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
doublej
7 - Meteor

Thanks Marquee, this gave me exaclty what i needed!

JohnJPS
15 - Aurora

Just out of curiosity for you Regex gurus, the following will handle single-digit month and days...

IIF(Contains([f1],'-'),[f1],
    REGEX_Replace([f1],"(\d{1,2})/(\d{1,2})/(\d{4})","$3") + '-' + 
    Right('0' + REGEX_Replace([f1],"(\d{1,2})/(\d{1,2})/(\d{4})","$1"),2) + '-' + 
    Right('0' + REGEX_Replace([f1],"(\d{1,2})/(\d{1,2})/(\d{4})","$2"),2))

But it's pretty ugly... is there a way to modify your original "$3-$1-$2" format to include the '0' padding "Right" approach within Regex output syntax?

 

jdunkerley79
ACE Emeritus
ACE Emeritus
Right(REGEX_Replace(
   "0" + Replace([Field1],"/","/0"),
   "0?(\d{2})/0?(\d{2})/0?(\d{4})","$3-$1-$2"),10)

is my normal trick

 

Add 0's everywhere then regex it out

JohnJPS
15 - Aurora

That's cool, James.  In the meantime this is what I came up with:

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

 EDIT: OK, all done simplifying...

NeilR
Alteryx Alumni (Retired)

For those who'd prefer to avoid regex at all costs, the Cleanse tool can handle this conversion (with a select tool afterward to convert from string to date).

Untitled.png

MichalK
8 - Asteroid

Hi Neil,

 

That's a very useful macro.

I tried modifying it to handle additional date formats (put the formats on the 'Input Formats' List Box and added formulas that handle them) but it does not work. Could you tell me what do I need to do to expand the macro's functionality?

 

Thanks

Michal

Labels